Why can the partial sums in pivot chart differ from the real sum of expression?

[#1]

I've created a pivot table chart with a if-calculated expression and enabled "Show partial sums" option. The trouble is that the total sum doesn't match the real sum of the values... What can cause the trouble?

The expression is: if($(vSpb)<sum(cf_od_sz),if($(vSpb)<sum(cf_vv_sz),$(vSpb),sum(cf_vv_sz)),($(vSpb) - sum(cf_od_sz)-$(vSpbNA)))

[sunny_talwar · Accepted solution]

Try using Sum(Aggr())

Sum(Aggr(YourExpression, NOP, CID))

[#3]

Where should I use Sum(Aggr())? The sums in each row for every CID are correct. The wrong thing is the Total sum in the top row (you can check that by autosuming the whole column in the attached excel file) but as far as I know, I can't change the way of calculation for Partial Sums.

[stabben23]

Hi,

When use if in pivot, pivot tabel can handeling it rowwise, but can not aggregate in on total. Therefore is a Aggr function needed to sum up the total on each Dimension. You need to go through Your variables or maybe use the Expression itself instead of the variable to get it correct.

Because on total the Whole Expression is true, thats why.

[#5]

I've tried enclosing the whole expression in the Aggr's brackets and it worked. Thanks!

[#6]

Thanks for clarifying the matter with the pivot and if!