Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section. In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 2006 and 2007. For some months, there is data for two years only 2005 and 2006. In the Value area section are fields such as Net Amount, Quantity, Bonus etc. and the summarization function applied to them is SUM. There is no complication in creating the Pivot Table described above.
The actual requirement is to customise the Subtotal column of the Pivot Table as follows:
1. For the monthly subtotals, the Net Amount and Bonus figure are to computed as a difference of 2005 and 2006 i.e. SUM of quantity of 2005 – SUM of quantity of 2006. The Grand total column should be a a summation of individual subtotals.
2. Average Selling price for every year is to be computed as as Net Amount/(Ttl Bonus + Quantity). For the monthly subtotals, the figure is to be computed as
=(Net Amount of 2005/((Bonus of 2005+Quantity of 2005)) – (Net Amount of 2006/((Bonus of 2006+Quantity of 2006))
The Grand Total column is to be left blank for Average Selling Price,
As you can observe, the subtotal column (for the months) will have different formulas running for different Fields.
A conventional Pivot Table does not allow one to have custom formulas in the Subtotal columns. I have been able to resolve this problem by using the free Power Pivot add-in from Microsoft for Excel 2010 and higher versions.
You may refer to my solution in this workbook.
Here’s another example. Assume a dataset with three columns – Date, Manager and Amount. There are repetitions in the Data and Manager column. One may want to know the maximum amount per month per Manager. While this is easy to accomplish with a Conventional Pivot Table as well, the problem occurs in the Subtotal/Grand Total cells of a Pivot Table. The Subtotal/Grand Total cells assume the same function as has been used in the “Summarise Values field by”. So, while in the “Summarise values fields by” section, one may want to use the Maximum function, in the subtotal cell, one may want to use the sum function.
You may refer to my solution in this workbook.
Perform different calculations in the Subtotal/Grand Total column of a Pivot Table
{ 33 Comments }