Pivot table calculated field and subtotal



I have a table with Company, Item, Year, Percent, and Sales. I have a
calculated field in a Pivot table called PercentSales (= Percent * Sales).
The problem I'm having is the subtotal by Item is totaling PercentSales as
Sum of Sales * Sum of Percent. How do I get the subtotal by Item of the
PercentSales field to be either (Sum of Sales * Percent) or Sum of
PercentSales (and not Sum of Sales * Sum of Percent)? Thanks.



Debra Dalgleish

You can't change the behaviour of the subtotal for the calculated field.
If possible, create the PercentSales calculation in the source data,
then add that field to the pivot table, where it will total correctly.

Or, in the pivot table, change the summary function for the subtotal to
Sum, instead of Automatic. This will remove the subtotal for the
calculated field, and you could calculate it outside of the pivot table.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question