Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DISTINCTCOUNT

 

This Data is about the Monthly Serviced units. One unit can get serviced multiple times in a month or year. However, when I count the total unit serviced I do not want the same unit being accounted twice. The DISTICNT COUNT works great for the Grand Column Total, however the DISCTINCT Function do not work on the monthly count ?. So 7 and 5 is the correct Disctinct count for the respective model for the Year, but the monthly listed unit come up as the totalcount. Any help is appreciated.

 

Measure = CALCULATE(DISTINCTCOUNT(DATA[SerialNumber]))

 

Need Help

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Not very clear, if you are asking why total do not match, so distinct count is calculated again for total to that might not sum up.

     

    to sum up you need

     

     Measure = sumx(Values(DATA[Month]), CALCULATE(DISTINCTCOUNT(DATA[SerialNumber])))

    or


    Measure = sumx(summarize(DATA, DATA[Month], DATA[Model],"_1", CALCULATE(DISTINCTCOUNT(DATA[SerialNumber]))),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Here is the table and Desired Result for DISTINCT Count. 

       

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous 

        maybe you can create a column

        count = if(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Model]=EARLIER('Table'[Model])&&'Table'[serialnumber]=EARLIER('Table'[serialnumber])))>1,0,1)

        and put the new column in the value field

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Worked like a Charm. Many Thanks Mate. Cheers