Wrong general column total

[#1]

Hi,

I have a pivot table with a expression (column) called 'Prod.'. The expression is calculated with a condition :

=IF(GR='C',Est.*TME,sum(I_PRODUCCION))

Where 'Est.*TME' = 12.879 when GR='C' as you can see in the attached printscreen.

The problem is that total should be = 712.498 (A) + 42.168 (B) + 12.879 (C) = 767.545 instead of  712.498 (A) + 42.168 (B) = 754.666

Why does it not make the correct summation? What can I do to get the right Total?

Thanks

[johnw · Accepted solution]

Pivot tables don't do a sum of rows, but rather reevaluate your expression for the total row.  In this case, when doing the full total, GR is null - it has values A, B and C at the same time, which evaluates to null.  So it skips over the first part and just does the sum(I_PRODUCCION) for all rows, and so skips the GR = 'C' data.  To get an accurate sum of rows in a pivot table, you need to use this general form for your expression:

sum(aggr(your expression, your dimensions))

In your example, I'm guessing something like this:

sum(aggr(IF(GR='C',Est.*TME,sum(I_PRODUCCION)),GR,MCDO,MES,RG))

I'm not remembering exactly what it'll do with Est. and TME, though, so I don't guarantee that it works without some further modification.

[#3]

I think it remains skiping over the first part of the if expression. Now the values for 'C' are cero like its total. Thanks anyway.