Incorrect total in pivot table.

[#1]

Hi, everyone - This is my problem:

I have three dimensions:

- CostCenter

- Year

- Month

And four expressions:

- ABM Total (SUM(AMOUNT))

- ABM Currency (ABM_CURRENCY): USD / PES / -

- ABM Currency Value (IF([ABM Currency] = 'USD', AVG(CURRENCY_VALUE), 1))

- TOTAL ([ABM Total] * [ABM Currency Value])

(this is just part of the pivot table, of course)

The total from the TOTAL column does not work OK. It is as if it's just taking ABM TOTAL total and multipliying for ABM CURRENCY VALUE total, which, of course, is not what I want. I want the sum of its rows, instead.

I have read about similar issues in this community and about AGGR function and I think the solution has to do with it, but I just can't manage to show the right total.

Could anybody lend me a hand? Thank you very much!

[jerem1234]

That's because when you drill-down in your CostCenter dimension, you have some values with ABM_CURRENCY equal to 'USD'. For example:

In your previous post, CostCenter of 61, Year 2014, and Month of Feb, the ABM_CURRENCY is 'USD'. Therefore, the aggr would calculate this even when you are drilled up only at CostCenter. It will sum up the values within the dimension.

So when you are looking at it drilled up to only CostCenter, it is calculating (from your previous post) 0+69.500+0+0+423.686,85+157.050,79+....

Note this will not be the same value of TOTAL ABM when you are drilled up to only CostCenter. How do you know which values have 'USD' for ABM_CURRENCY and which don't?

How exactly do you want to calculate? Keep in mind when you are drilling down or when you are drilled up.

Hope this helps!