Ensure that “Show Value as” feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden

{ 9 Comments }

By Ashish Mathur · · , · Leave a comment

Let’s say the data sheet has Sales and Profit data by Product, Region, Salesman and Date.  One may want to analyse the following via a Pivot Table

“The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product.  Do the same for every month for two year (2012 and 2013).”

While this is easy to accomplish using the “Show Value As” feature of a Pivot Table, the problem arises when a particular year is filtered out from the Pivot Table.  This activity of filtering out a year from the Pivot Table causes the other computed Sales Delta figures to vanish.

This problem can be overcome by creating a Pivot Table from the PowerPivot tool of Excel 2010 and higher versions.

You may refer to the question, Pivot Table attempt and PowerPivot solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

9 Trackbacks

  • Ashish,

    As per your request I’m reposting my question from the Microsoft Community website.

    Please see this file. http://tempsend.com/F109398652

    Worksheet “Raw Data” is service desk data. In Worksheet “Pivot chart” I’m trying to display the percentage that SLA resolution=pass/total number of tickets, per Priority over time in a line chart. I can’t work out how to generate that data without also displaying the fail percentage. Percentage fields are currently being calculated as % of Parent Row Total so, obviously, when I remove “Fail” everything is set to 100%.

    I have a feeling that calculated fields are going to be the way to go but I can’t get my head around how to calculate what I am after.

    Your help is appreciated,

    GDXT

    Reply

  • Thanks Ashish,

    That’s perfect. I have to use Excel 2010 at work so I’ve been translating your work to that version. I’ve got everything working expect for the sort order of the months. Its defaulting to alphatbetical ordrer.

    In PowerPivot I’ve configured the extra cloumns in the Data table and the Order column is populating via the RELATE formula. I’ve set up a PivotTable with the same parameters as your example but the months are sorted by alphabetical order. I can’t work out why your table knows to sort the row labels by Year then Order.

    What am I missing?

    Thanks,
    Grant

    Reply

  • Hi again,

    I’ve found the “Sort By Column” setting in your document. As far as I can tell that function doesn’t exist in the 2010 version.

    As an alternative I’ve configured a custom number format (01 for Jan, 02 for Feb, etc) for ‘Month Order'[Order] and changed the formula for ‘Data’Order to display 01-Jan, 02-Feb etc.

    I’m sure there is a slicker way of doing this but it works for me.

    Thanks again for your help. This has been a hurlde for a couple of weeks.

    Cheers,
    Grant

    Reply

  • It seems I had downloaded an old version of PivotPoint. I found the latest version and I’ve got the Sort To Column.

    In my full data set I also need to report on “Opened Date”. I’m trying to apply the same Month Order methodology but you aren’t allowed to set up a second relationship.

    How can I accomodate this?

    Reply

  • Hi Ashish

    I’m trying to figure out how to display percents for certain fields in a pivot table and I’m stuck. In my raw data, I get totals and I’d like to show on a worksheet the comparison of those totals per month. Do you have any idea how to do that?

    Thanks!

    Reply

    • Hi,

      I cannot understand your question unless I see the workbook. Upload the workbook to OneDrive and share the link of the workbook here. Please explain your desired result very clearly.

      Reply