Forum Discussion
SUM column based on distinct ID
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!
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)] )
2 Replies
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)] )