Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi Pavel,
Thanks for replying! 🐵
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
Hi, @Johnny
Based on my test, if I added a matrix visualization to aggregate data, the total value displayed as -80 correctly. So, what is the entire measure for Total like in your scenario? Would you please share your pbix file for more analysis if possible?
Best regards,
Yuliana Gu
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |