Forum Discussion

Ashley's avatar
Ashley
Helper II
3 years ago
Solved

Distinct 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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
Ashley
Helper II
3 years ago

Thank you for your response, it actually worked perfectly using SUMX(DISTINCT(.....