How to subtract subtotal(s) from Grand Total in PivotTable?

[#1]

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, Dan

[Jason_Michaelides]

Please post your app showing clearly what you want to achieve.

[#3]

Does this help?

[#4]

Does this help?

[Jason_Michaelides]

Very difficult to follow this kind of request without a sample app.  You will often find you get better help if you take the time to build one.

However - have you tried a simple IF statement?

IF(Dimensionality()<2,0,sum(aggr($(vExp_TrueUp),Country, State, City)))

Jason

[#6]

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. 

Thanks again for taking a look.

[#7]

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.

[Jason_Michaelides]

Hi mate,

Sorry - I've been really busy lately.  I'll try and take a look at this tonight or tomorrow for you.

Jason

[Jason_Michaelides]

Hiya,

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.

Hope it helps,

Jason