Sum values by category in table

[bo_afk]

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

[Anonymous]

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

[Anonymous]

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?

[amitchandak]

@bo_afk , Try like

Measure = CALCULATE(SUM('Table (2)'[Sales]), REMOVEFILTERS('Table (2)'[Category1],'Table (2)'[Category2]))

[bo_afk]

Hi @amitchandak, thanks for your suggestion. Unfortunately this hasnt completely worked. It sums it up by productID but not by category 2.