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.
Ensure that “Show Value as” feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden
{ 9 Comments }