Different partial sum in Pivot table based on dimensionality

[#1]

I have a pivot table with rows zero suppressed based on below condition for second expression:

if(exp1<>0, exp2)

there are 2 dimensions and i have a requirement to write different partial sum expression based on dimensionality.

1) for partial sum on dim1, i.e dimensionality=0, it should sum up all the values that exp2 evaluates to, even when exp1=0.

2) for partial sum on dim2, i.e dimensionality=1,. it should sum up only those values that exp2 evaluates to , when exp1 <>0

Please refer to sample table image attached.

Total at level 2 should only add those exp2 values where exp1<>0 , hence 40. Total at level 1 should add up all values for exp2, even when value for exp1 =0, hence it should be 80.

Please let me know how i can achieve this

[swuehl · Accepted solution]

Maybe something like

=If(Dimensionality() = 0,

     Sum(Aggr( YourExpression2, Dim1, Dim2)),

     Sum(Aggr( If( YourExpression1 <>0, YourExpression2), Dim1, Dim2))

)

[#3]

Thanks!that worked.