1 Reply Latest reply: May 24, 2012 4:36 AM by Stefan Wühl RSS

    sub total

    Nick Gan

      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?

       

      1552.jpg

        • sub total
          Stefan Wühl

          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)

          )