Forum Discussion
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
Here is what I am looking for
| Product | Count of Status On | Counr of Status Off | Status On % | Status Off % |
| A | 1 | 2 | 25% | 75% |
| B | 2 | 0 | 100% | 0% |
| C | NA | NA | NA | NA |
Here is what I have
| Product | Sevice ID | Status |
| A | 14 | ON |
| A | 14 | ON |
| A | 21 | OFF |
| A | 21 | OFF |
| A | 22 | OFF |
| B | 8 | ON |
| B | 9 | ON |
| C | 11 | Not Applicable |
Hi,
For ProductA, the Status on % should be 1/3 i.e. 33.33%. Am i correct?
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]))Hi Amitchandak - Thank you very much!!!
John
7 Replies
Hi johndolan2
What you're looking for is easily achievable with a Matrix visual.
The measures would look something like this:
DistinctCountOn =
CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), 'Table'[Status] = "On" )
DistinctCountOff =
CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ), 'Table'[Status] = "Off" )
% On =
DIVIDE (
CALCULATE ( COUNT ( 'Table'[ID] ), 'Table'[Status] = "On" ),
COUNT ( 'Table'[ID] )
)% Off =DIVIDE(CALCULATE(COUNT('Table'[ID]),'Table'[Status] = "Off"),COUNT('Table'[ID]))Hi,
For ProductA, the Status on % should be 1/3 i.e. 33.33%. Am i correct?
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
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.
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]))Hi Amitchandak - Thank you very much!!!
John
