Forum Discussion

18520148's avatar
18520148
Frequent Visitor
4 years ago
Solved

Help to solve distinct value in month

Please help me some Power BI Experts, I have problem like the first picture when I extracted data. My table with the ID repeated the values each month

 

 

Can anyone help me to write a DAX formula to Sum the distinct value group by ID and each Month like following table. Thank you very much.

 

DAX
DAX Commands and Tips
power bi
PowerBI Community
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your data model correctly without seeing the actual one, but please try the below measure.

     

    Distinct value sum measure: =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'table name', 'table name'[MONTH], 'table name'[ID] ),
        "@distinctvalue", CALCULATE ( MAX ( 'table name'[VALUE] ) )
    ),
    [@distinctvalue]
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your data model correctly without seeing the actual one, but please try the below measure.

     

    Distinct value sum measure: =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'table name', 'table name'[MONTH], 'table name'[ID] ),
        "@distinctvalue", CALCULATE ( MAX ( 'table name'[VALUE] ) )
    ),
    [@distinctvalue]
)
    • 18520148's avatar
      18520148
      Frequent Visitor
      4 years ago

      It's really help me. Thank you so much.