Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I've created a measure to sum my values grouped by 2 categories using the SUM and ALLEXCEPT functions, however it's not calculating the numbers as expected.
Table
Category2 | Category1 | ProductID | Sales |
1 | Bag | ABC123 | 100 |
2 | Bag | ABC123 | 200 |
1 | Bag | XYZ456 | 300 |
2 | Bag | XYZ456 | 400 |
1 | Shoes | DEF567 | 800 |
2 | Shoes | DEF567 | 700 |
Measure = CALCULATE(SUM(Value)),ALLEXCEPT(Table,[Category1],[Category2]))
When I put the values into the table and include category 2, the correct value is calculated. However, when I remove category2 from the table, the figures change. I would've expected the grouped sum up to category2 to remain but it doesn't.
How can amend my measure to display the expected outcome below?
Current outcome (incorrect)
Exected outcome
Category1 | ProductID | Sales | Measure |
1 | ABC123 | 100 | 400 |
2 | ABC123 | 200 | 600 |
1 | XYZ456 | 300 | 400 |
2 | XYZ456 | 400 | 600 |
1 | DEF567 | 800 | 800 |
2 | DEF567 | 700 | 700 |
Hi @bo_afk,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello @bo_afk ,
You better create column instead of measure and try,
Hi @Krutigawale33, thanks for the suggestion. This does seem to work however this doesn't dynamically calculate if I apply other filters in the report. Am I really unable to create this as a measure so that the figure changes with filters?
@bo_afk , Try like
Measure = CALCULATE(SUM('Table (2)'[Sales]), REMOVEFILTERS('Table (2)'[Category1],'Table (2)'[Category2]))
Hi @amitchandak, thanks for your suggestion. Unfortunately this hasnt completely worked. It sums it up by productID but not by category 2.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |