Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SUMX Distinct and GroupBy

Hi I have a table with Month, Week and number of records. What I would like to do is Sum(Distinct(Records)) groupby MonthNo, WeekNo.   I have a join with another table using MonthNo and WeekNo. So ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous ,

     

    The correct result should be 10085. There could be two solutions. The basic idea is including the MonthNo and WeekNo at the same time. 

    Measure 2 =
SUMX ( ALL ( DB[MonthNo], DB[WeekNo] ), CALCULATE ( MAX ( DB[Records] ) ) )

    Measure 3 =
SUMX (
    SUMMARIZE ( DB, DB[MonthNo], DB[WeekNo], "maxRecords", MAX ( DB[Records] ) ),
    [maxRecords]
)

    SUMX-Distinct-and-Group-By

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous ,

 

The correct result should be 10085. There could be two solutions. The basic idea is including the MonthNo and WeekNo at the same time. 

Measure 2 =
SUMX ( ALL ( DB[MonthNo], DB[WeekNo] ), CALCULATE ( MAX ( DB[Records] ) ) )

Measure 3 =
SUMX (
    SUMMARIZE ( DB, DB[MonthNo], DB[WeekNo], "maxRecords", MAX ( DB[Records] ) ),
    [maxRecords]
)

SUMX-Distinct-and-Group-By

 

Best Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks v-jiascu-msft  got the desired result now.