Forum Discussion
Helper IIDistinct Sum of Values
Hello,
This may be a basic question but I'm not quite sure how to achieve it. I have a table with a master category count column that has counts duplicated for each brand. Is there a way to do a distinct sum of only one of each value, so the highlighted values only? I can't remove duplicate values because I need each unique brand to remain present, but I don't want to quadruble my master number count. Each Master count represents a different total within a specific category, that's why you see the brands repeated in the brand column.
Any help would be greatly appreciated.
Thank you!
Ashley Well, if it is a column you could do this:
Measure = SUMX(DISTINCT('Table'[MasterCategoryColumn]),[MasterCategoryColumn])If it is a measure, This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
3 Replies
- Greg_Deckler
Community Champion
Ashley Well, if it is a column you could do this:
Measure = SUMX(DISTINCT('Table'[MasterCategoryColumn]),[MasterCategoryColumn])If it is a measure, This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.- Ashley
Thank you for your response, it actually worked perfectly using SUMX(DISTINCT(.....
- Ashley
Nvm, I think I figured it out!
using SUMX(DISTINCT(.....
