Problem with TOTAL in Pivot

[picturebox]

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

[swuehl · Accepted solution]

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

[picturebox]

Hi,
works like a charm. I had tried to make sense of the aggr() function before, now I see the use and results.
Thanks a lot!
Petra