SUM column based on distinct ID

Anonymous — Wed, 26 Jul 2023 12:47:21 GMT

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!

Re: SUM column based on distinct ID

ERD — Wed, 26 Jul 2023 13:26:33 GMT

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)] )

Re: SUM column based on distinct ID

Anonymous — Wed, 26 Jul 2023 13:50:25 GMT

Worked like a charm!

Thanks for the time and help! 🙂

topic Re: SUM column based on distinct ID in DAX Commands and Tips

SUM column based on distinct ID

Re: SUM column based on distinct ID

Re: SUM column based on distinct ID