Forum Discussion
Post PatronSum values by category in table
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 |
5 Replies
- amitchandak
Super User
bo_afk , Try like
Measure = CALCULATE(SUM('Table (2)'[Sales]), REMOVEFILTERS('Table (2)'[Category1],'Table (2)'[Category2]))
bo_afkHi amitchandak, thanks for your suggestion. Unfortunately this hasnt completely worked. It sums it up by productID but not by category 2.
Hello bo_afk ,
You better create column instead of measure and try,
Column = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Sheet15[Category1],Table[Category2]))- bo_afk
Hi Anonymous, 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?
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
