rolling up totals in pivot table

[nickm]

Issue: Individual lines do not roll up with the correct total - see attached file column highlighted in gray.

Chart Type: Pivot Table

Background: We are taking the 2014 GP% calculation(2014_Margin_Dls/2014_Sales) as base starting point. There are specific product codes with 2 up charges (2 fields) which are added to the 2014 GP%. We then divide the 2014_GP% into the 2015 Budget to create the 2015_GP%_Goal. If there are no Sales for 2014 for a specific product code I multiply the 2015 Budget by .20 (20%). This is the issue, the individual lines are correct but the total rolls up incorrectly.

The calculation is in the column highlighted in gray (GP$ w Resin adj), any suggestions are greatly appreciated!

Thx - Nick

[rwunderlich · Accepted solution]

I think you are seeing the "Sum of Rows in Pivot Table" problem. Search the help for "Advanced aggregation" and you'll find a link to a Sum of Rows example.

The quick fix in your case is to enclose the entire expression in another sum(aggr()) like:

sum(

aggr(

...your current expresion...

,Territory,Customer_ID,Product_Code)

)

Example attached.

-Rob

http://masterssummit.com

[nickm]

Rob,

Thank you very much for the correction..greatly appreciated.

Nick