Forum Discussion
Calculate then Aggregate
Hi, I'm trying to find a way to keep a calculation on a detailed grain and once it's done on this level then aggregate it (SUM) the rest of the way to the top. The formula is basically a X*Y...
Hi Pavel,
Thanks for replying! :o)
My bad - if you take the information out in a pivot table I see this:
I have data like this:
| Product | Customer | Y Value | X Value |
| a | x | 10 | 2 |
| a | y | 20 | 4 |
| a | z | -30 | 6 |
| b | x | 35 | 1 |
| b | y | -80 | 3 |
| b | z | 45 | 5 |
| c | x | -45 | 9 |
| c | y | 60 | 8 |
| c | z | -15 | 7 |
And a measure which is X*Y
This gives me this result in a pivot table for illustration:
| Sum of Result Pivot | ||
| Product | Customer | Total |
| a | x | 20 |
| y | 80 | |
| z | -180 | |
| a Total | 0 | |
| b | x | 35 |
| y | -240 | |
| z | 225 | |
| b Total | 0 | |
| c | x | -405 |
| y | 480 | |
| z | -105 | |
| c Total | 0 | |
| Grand Total | 0 |
Product a)
Clearly the 20+80-180 results in -80 and not 0 as above - and I want it to say -80.
One caveat is that I can't calculate the total in a column - as it is a somewhat complex measure and does not provide me with the correct result.
Hope it makes better sense??
Rgds,
J
I used your data and I have this result:
I just created Total measure: Total = SUMX(Data;Data[X Value]*Data[Y Value])
and I used it as Value in pivot table.
Hope it is what you wanted.
Regards.
Pavel