Forum Discussion

jcastr02's avatar
jcastr02
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Summing with distinct values

how can i get a sum of all values of three columns (minor, major, critical) but only distinct values based on the ID column.  My data has repeating rows and I can't remove because I'm using other fields value.   (ex. since ID 14 is repeating, we would only count that once)

 

IDMinor TotalMajor TotalCritical Total
12210
10021
111110
13333
14401
14401
15500
14401
14401

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    jcastr02 , Create measure like this for all three columns

     

    sumx(summarize(Table,Table[ID], Table[Minor Total]),[Minor Total])

     

    sumx(summarize(Table,Table[ID], Table[Major Total]),[Major Total])

     

    sumx(summarize(Table,Table[ID], Table[Critical Total]),[Critical Total])

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    jcastr02 , Create measure like this for all three columns

     

    sumx(summarize(Table,Table[ID], Table[Minor Total]),[Minor Total])

     

    sumx(summarize(Table,Table[ID], Table[Major Total]),[Major Total])

     

    sumx(summarize(Table,Table[ID], Table[Critical Total]),[Critical Total])

  • mhossain's avatar
    mhossain
    Icon for Solution Sage rankSolution Sage
    5 years ago

    jcastr02 

     

    In adition to amitchandak  's dax, you can also try below measure:

    DistinctCount based on 3 cols =
    CALCULATE(DISTINCTCOUNT('Table'[ID]) , 'Table'[Minor Total]>0)
    +
    CALCULATE(DISTINCTCOUNT('Table'[ID]) , 'Table'[Major Total]>0)
    +
    CALCULATE(DISTINCTCOUNT('Table'[ID]) , 'Table'[Critical Total]>0)
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    jcastr02 

    Try Amit's codes and add up all of them into a measure like below, this should work.

    Measure = sumx(summarize('Table','Table'[ID], 'Table'[Minor Total]),[Minor Total]) + sumx(summarize('Table','Table'[ID], 'Table'[Major Total]),[Major Total]) + sumx(summarize('Table','Table'[ID], 'Table'[Critical Total]),[Critical Total])

    You can also try this measure:

    Measure 2 = SUMX(SUMMARIZE('Table','Table'[ID],'Table'[Minor Total],'Table'[Major Total],'Table'[Critical Total]),'Table'[Minor Total]+'Table'[Major Total]+'Table'[Critical Total])

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.