Problem With Pivot Table Calculated Field Totals
You can create a calculated field in a pivot table, to perform calculations based on the Sum of a pivot table field. For example, this formula would calculate a bonus based on the total number of units sold for each product. If 60 or more were sold, then the salesperson would receive a 3% bonus on the sales amount for those products.
=IF(Units>= 60,Total* 3%,0)
In the pivot table shown below, Andrews sold 150 binders, and earned a bonus of 22.46 on that product. Gill only sold 10 pens, so there is no bonus earned for that.
The headings in the pivot table have been changed:
- Sum of Total --> Sales
- Sum of Units --> Units Sold
- Sum of Bonus -->Bonus Amt
Calculated Field Totals
After creating the Bonus calculated field, you might expect to see a sum of the bonus amounts, in the subtotal and grand total rows. However, the calculated field uses the same calculation in the subtotal and grand total rows, instead of showing a sum.
In this pivot table, Gill had two bonus amounts -- 11.98 and 5.38, for a total of 17.36. However, the subtotal for Gill shows a bonus amount of 26.21, which is too high.
The grand total for a calculated field performs the same calculation that’s defined in the calculated field. So, for Gill, instead of summing the bonus amounts, it calculates 3% of Gill's total sales -- 874 * 3% = 26.21
Calculate Outside the Pivot Table
Unfortunately, there's no setting that you can change in the pivot table, to sum the calculated fields, instead of using the calculated field formula on the totals.
As a workaround, you could use formulas outside the pivot table to extract the bonus amounts, and sum those amounts.
- To create blank cell in the product column, the report layout was changed to Outline Form.
- To create salesperson groups, in cell F5, the formula is: =--(B5="")+F4
- (Note: there are 2 minus signs after the equal sign in the above formula)
- In cell G5: =IF(B5<>"",E5,0)
- The bonus calculation in cell H5 is:
=IF(A5="Grand Total",SUM(G:G),IF(B5="",SUMIF(F:F,F5,G:G),G5))
Then, hide the pivot table column that contains the Bonus calculations, so only the column with the correct subtotals and grand total is visible.
Download the Sample File
To work with the data in this example, and create the calculated field, you can download the Calculated Field Bonus sample file. The file is in Excel 2007 format, and zipped.
_____________
The workaround would be even better if you use Get Pivot Table Data feauture.
It seems to me that the workaround also assumes that the pivot table is stable. If you have an unknown number of rows, it’s still problematic.
@Paul, you’re right, if the pivot table will change size, you could add the formulas with a macro that runs automatically when the pivot table changes.
A much better solution would be to calculate the bonus amount before creating the pivot table – either in the sql or on the original worksheet. Then everything will show correctly on the pivot table, no matter the changes to the layout.
I find calculated fields are most useful for calculations involving percenatges – e.g. calculating percentage growth. For these problems, calculating the percentage change works, but all the summary information is wrong. That’s because it sums the percentages – not what is wanted. Even using average instead of sum is not helpful, because you usually need a weighted average, not a simple average. Calculated fields work great for these problems for the same reason it caused the problem in the article – for instance, the grand total is calculated using the formula in the calculated field; exactly as desired.
I have following pivot table:
Region Zone #of customers Sales Sales/sub Totals of ZONE
south 1 50 $ 1000
total 1 50 $1000
2 60 $ 1500
total 2 60 $1500
total south 110 $2500
now i want to get a %age sales to the total # of customers in ZONE, which has sub totals 1 & 2.
Can anybody help me and give the solution. My email address is mjunaid6@hotmail.com.
Thanks.
Junaid
Debra – If you still have it, will you attach the file you used for this post as well? (Can you tell I’m focused on calculated fields?
)
Sure Bob, I’ve uploaded the sample file for this post. Hope you’re having fun with the calculated fields!
I just thought I would post a relevant bug report from Microsoft which seems to explain why this useful workaround is required.
http://support.microsoft.com/kb/21147
Corrected link:
I just thought I would post a relevant bug report from Microsoft which seems to explain why this useful workaround is required.
http://support.microsoft.com/kb/211470
Can anyone help with this… I have entered a calculated field to give me a calcuation of Gross Profit % (of total sales and gross profit £) but it is only entered a figure in the total column – really need it by month (all other columns) – anyone help please?….
can I have a display option using a pivot table where i need to display only the customers who have not given orders in a particular month amongst the ytd data
Hi
Not sure if this helps but i had a similar problem calculating a £value based on a sumed up stock level (calculated as a demand figure * 2 which i called “Level”) and a cost price. Excel was summing up the costs as well as the qualtities. The solution looks very odd but works.
I added an extra column on my data which just contains 1′s which i called “Line”. This then allowed me to do the following as a second calculated field.
=(Level*(‘Invoice Cost’/Level)/Line)*Level
Hope this is of help
Steve
Appologies, just realised i pasted my first attempt, my someone shortened version is this:
=((‘Invoice Cost’/Line)*Level)