Pivot total with calculated dimension

[#1]

Hello,

I have a pivot table with 2 dimensions: "Year", and a calculated dimension. I need to add a total row at the bottom of the table (sum of all the rows in the table). Any idea?

Thanks

[Oleg_Troyansky]

The usual "Show Subtotals" setting (Presentation tab) should still work, despite the calculated dimension. Are there any hidden issues?

[#3]

Hi Oleg, the "Show Subtotals" is on, but the totals are incorrect....

[Oleg_Troyansky · Accepted solution]

This maybe be caused by the nature of your expression. The "totals" in the pivot table are always evaluating the same expression at the higher level of aggregation, and that's not always "correct" or the result that you expected to see.

The "common" solution is to use AGGR and to force the calculation at a certain level of detail, before aggregating up. This is impossible to do with a calculated dimension. I see two options:

1. If the calculated dimension can be uniquely identified with a single field, than that single field could be used in the AGGR, instead of the calculated dimension.

2. Otherwise, look for a way to avoid the calculated dimension and replace it with a static dimension, calculated in the script. Calculated dimensions are bad for performance anyway.

If none of those options are feasible, then it may not be possible to add a total line at the bottom.

cheers,

Oleg Troyansky

Check out my lecture on Set Analysis and Advanced Aggregation at the Masers Summit in Chicago!

www.masterssummit.com

[#5]

You solved the problem. thanks