Forum Discussion

johndolan2's avatar
johndolan2
Resolver I
5 years ago
Solved

Distnict count multiple columns

Hi -  am trying to come up with measures to show a distinct total and a percentage of total for the table below labeled "Here is what I have". If you could help that would be great. Thanks, John   ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    For ProductA, the Status on % should be 1/3 i.e. 33.33%.  Am i correct?

  • amitchandak's avatar
    amitchandak
    5 years ago

    johndolan2 , % for A does not seems correct, Try like

     

    Count of Status On = calculate(distinctcount(Table[Sevice ID]) , filter(Table, Table[Status] = "ON"))
    Count of Status Off = calculate(distinctcount(Table[Sevice ID]) , filter(Table, Table[Status] = "OFF"))

    Off Status On % = divide([Count of Status On], [Count of Status On] +[Count of Status Off])
    Status Off % =divide([Count of Status Off], [Count of Status On] +[Count of Status Off])

    or

    Off Status On % = divide(calculate(count(Table[Sevice ID]) , filter(Table, Table[Status] = "ON")), count(Table[Sevice ID]))
    Status Off % =divide(calculate(distinctcount(Table[Sevice ID]) , filter(Table, Table[Status] = "OFF")) , count(Table[Sevice ID]))

  • johndolan2's avatar
    johndolan2
    5 years ago

    Hi Amitchandak - Thank you very much!!!

    John

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

For ProductA, the Status on % should be 1/3 i.e. 33.33%.  Am i correct?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I was thinking there was an error in OP's original table - definitely seems like it shouldn't be 25%, but to me it looks like it should be 40% (2/5).

  • johndolan2's avatar
    johndolan2
    Resolver I
    5 years ago

    Hi Ashish Yes, product A should be 1/3 i.e. 33.33%.  .... I was just in the process of fumbling around trying to figure out how to get that number in a measure.  You have been great ... How do I now get that 33.33% in a measure. Thanks, John

  • johndolan2's avatar
    johndolan2
    Resolver I
    5 years ago

    Hi Ashish and Ebeery - to get the % totals I did this: I created a measure to sum the two measures of "distinct count on" with the "distinct count off". I then divided that number against the "distinct count on" and then against the "distinct count off". The %s look good to me. I just want to say thank you! thank you! and thank you! I had been fumbling for hours.