How to subtract subtotal(s) from Grand Total in PivotTable?
I have a pivot table with three Dimensions, (Country, State, City). Thus there are three levels of Dimensionality (0=Grand Total, 1=Country Total, and 2=State Total. Here’s my problem….for one particular city, when that value is greater than “n” I want that state’s subtotal to be 0, thus I want the higher subtotals (Dimensionality 1 and 0) to NOT include the value for the corresponding State.
The formula I am currently using that gives totals/subtotals for ALL cities/states is: sum(aggr($(vExp_TrueUp),Country, State, City)). I’ve tried numerous formulas and have searched the forums tirelessly. Can you help me with a solution?
Thanks for the suggestion, Jason. I may just have to build a sample app, though it may take some time.
I have used that exact formula for Dimensionality()=2 - and it works...however, by taking this actual subtotal, say it's 100, and changing it to 0, I now have to figure out a way to subtract 100 from Dimensionality()=1 and Dimensionality()=0.
For example, in the image I posted above, I'm forcing the US subtotal of $112,830 to $0, but to make sense to the users, this $112,830 needs to be subtracted from Company A subtotal ($157,023) and from the Grand Total ($157,023).
I'm thinking the solution may be some "If" statements within the AGGR function/formula posted above.
I tried to mock-up a sample app with which to try and solve this problem (see attached). I'm trying to aggregate a Variable, then customize the 3 Dimensionalities. Thanks.
I have had another go at this tonight. I have a solution using a straight table but not a pivot. Was edging closer to a pivot solution (I think) but it's late now!
In a straight table you can use "Sum of Rows" as an option for the expressions...see attached.
