Distnict count multiple columns

[johndolan2]

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

[Ashish_Mathur · Accepted solution]

Hi,

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

[amitchandak · Accepted solution]

@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]))

[amitchandak · Accepted solution]

@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 · Accepted solution]

Hi Amitchandak - Thank you very much!!!

John

[Ashish_Mathur · Accepted solution]

Hi,

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

[johndolan2]

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]

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

[ebeery]

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).

[ebeery]

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])

)