2 Replies Latest reply: Feb 1, 2012 10:41 AM by Petra Bernehed RSS

    Problem with TOTAL in Pivot

    Petra Bernehed

      Hi all,


      In a pivot chart I have a number of dimensions and then the calculated column Sum Outlets. At the lowest level the calculation is correct, this is what I am using:

      = if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR))

       

      It makes sense in QV that the total for a dimension uses the formula as above, but what I really want is the total of the rows for the dimension and column Sum Outlets based on the values calculated. If I do a quick change to flat table, the totals are set on "rows", the total is correct. How can I get that to function in a pivot table?

      Thanks,Petra

        • Problem with TOTAL in Pivot
          Stefan Wühl

          If I understand your problem correctly, you need advanced aggregation to get the sum of rows in your pivot table (there is example in the Help, search for sum of rows in pivot table), like

           

          =sum( aggr( if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR)) , YOURTABLEDIMENSION1, YOURTABLEDIMENSION2))

           

          Just add all your dimensions of the table to the aggr dimension list.

           

          Expression should also work in a straight table.

           

          Hope this helps,

          Stefan