sub total

[#1]

Hi,

I have a pivot table shown like below.

the percentage for inidividual row is correct.

but when it comes to TOTAL, the 94.05% is not right.

it should sum ONLY the value for MAR & APR.

Feb and May should be excluded in the total.

help?

[swuehl]

Try checking for the total line with dimensionality() and then filter out the lines where either column1 or column2 is zero (Expr1, and Expr2 are the expressions used in column1 and column2, maybe like sum(Cost) and sum(Bduget) 😞

=if(dimensionality()=0,

sum(aggr( if( Expr1 > 0 and Expr2 > 0, Expr1), ALLYOURDIMENSIONS)) / sum(aggr( if( Expr1 > 0 and Expr2 > 0, Expr2), ALLYOURDIMENSIONS)) /

, column(1)/column(2)

)