Total Sum in Pivot Table

[orital81]

Hi

I have an expression which has a different total in the pivot totals expression.

I would like to adjust the expression to fit pivot table total sum calculation.

The top table (straight) has a Correct Total Sum

the table below is a pivot which has a different sum

Expression:

If(Sum({<Proj_type = {'scc'}>} Sum_Expense)=0,sum({<Proj_type = {'mcc'}>}Sum_Expense),sum({<Proj_type = {'scc'}>}Sum_Expense))

See my attached example, user/pass: servit/servit123

Thanks

[Anonymous]

The straight table has total as "sum of rows".  If you change it to "expression total", the result is the same as in pivot table.

In situations like this, in most cases the "Expression total" is a "more correct" result, if I may say so.  For example, some data may be correctly taken into account in more than one row.  If you use "sum of rows", you get it double-counted.  "Expression total" eliminates double-counting.

Of course I am not aware about the specifics of your rules.  Just explaining the difference.

Regards,

Michael

[jonathandienst · Accepted solution]

Hi

To get a 'sum of rows' calculation in a pivot table, use an aggr like this:

Sum(Aggr(If(Sum({<Proj_type = {'scc'}>} Sum_Expense)=0,sum({<Proj_type = {'mcc'}>}Sum_Expense),sum({<Proj_type = {'scc'}>}Sum_Expense)), PivotDim1, PivotDim2,...))

Replace PivotDimn with all the pivot table dimensions, separated by commas.

HTH

Jonathan

[orital81]

Thanks Jonathan, your suggestion works!

[orital81]

Thanks Michael, this was helpful.