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 (different grains though) and this works fine on the lowest level - so far so good.
Now I want to sum these results (eg. a total for all customers) and this results in 0 because the Y on this level is 0 - makes sense to me. Hence I want to CALCULATE (on product and customer level) first then AGGREGATE the calculated result in stead of "just" calculating it on Customer.
My table looks something like this:
| Calculation (X and Y Values are Measures) | |||||
| Customer | Product | Y Value | X Value | Current Result | Desired Result |
| 1 | 1 | 10 | 1 | 10 | 10 |
| 2 | 1 | 20 | 2 | 40 | 40 |
| 3 | 1 | -30 | 3 | -90 | -90 |
| 1 | 2 | 5 | 4 | 20 | 20 |
| 2 | 2 | 10 | 5 | 50 | 50 |
| 3 | 2 | -15 | 6 | -90 | -90 |
| 1 | 3 | -15 | 7 | -105 | -105 |
| 2 | 3 | -30 | 8 | -240 | -240 |
| 3 | 3 | 45 | 9 | 405 | 405 |
| Aggregations | |||||
| Customer | Product | Y Value | X Value | Current Result | Desired Result |
| 1 | 0 | 12 | 0 | -75 | |
| 2 | 0 | 15 | 0 | -150 | |
| 3 | 0 | 18 | 0 | 225 | |
| 1 | 0 | 6 | 0 | -40 | |
| 2 | 0 | 15 | 0 | -20 | |
| 3 | 0 | 24 | 0 | 60 |
I've tried to use this formula:
SUMX(
SUMMARIZE(
Customer;
Product);
X*Y)
This results in 0 in all levels (both higher and lower grain).
I can find plenty resources explaining how I aggregate then calculate - but not calculate then aggregate.
Hope anyone can help out - it will be greatly appreciated.
Kind regards,
J.
4 Replies
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
