SUM column based on distinct ID

[Anonymous]

Hi all!

I cant seem to figure out the following (which looks very straightforward)

I have a dataset which shows how much meter a category has in a store. 

The data set has rows for each article within a category, but the 'Length of Category' column is based on the total of that category.

For example, in store 1, the Bread Category has 2,0 meter of space.

Store ID	Category	Article	Length of Category (in meters)
1	Bread	White	2,0
1	Bread	Brown	2,0
1	Drinks	Water	4,0
1	Drinks	Soda	4,0

For the example above, if I sum how much total meters store 1 has, it will add up to 12,0 (2+2+4+4), but I need it to be 6 (2+4). As I dont want the categories to double.

What is the correct way of doing this through dax? I cant seem to google it or figure it out and I feel like it's quite easy.

Help is much appreciated!

[ERD · Accepted solution]

Hi @Anonymous ,

You can try this measure:

meters per store = 
VAR t = SUMMARIZE ( 'Table', 'Table'[Store ID], 'Table'[Category], 'Table'[Length of Category (in meters)] )
RETURN
    SUMX ( t, [Length of Category (in meters)] )

[Anonymous]

Worked like a charm! 

Thanks for the time and help! 🙂