Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table calculated field and subtotal
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. Larry wrote: 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 Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotaling a calculated field in a pivot table | Excel Discussion (Misc queries) | |||
calculated field - Pivot Table | Excel Worksheet Functions | |||
Subtotal in a pivot table | Excel Discussion (Misc queries) | |||
pivot table : formula to absolute reference a subtotal | Excel Discussion (Misc queries) | |||
pivot table - hide details but show subtotal for calculated field | Excel Discussion (Misc queries) |