Need to get % of a particular value of a column but using a distinct count of another column

[Anonymous]

I'm not sure if this is best handled in query, or if by a measure, or what.

I have 2 tables: Table 1 is a list of clients, each with a unique Client ID# (MRN), and Table 2 is a list of encounters that happened with a column indicating a condition of "compliant", "non-compliant", or "n/a".  An "n/a" means that the item should not be counted for the denominator.

I have merged the two tables into Table 3, so I now have duplicate client IDs due to the multiple encounters.  The date relationship is keyed to the EOQ date (trending by quarters)

So - I need to count the number of "compliant", divide by the total of "compliant" + "non-compliant" to get the percent of compliant clients within the quarter, but with a distinct count of the Client ID#.  Sample data below:

Thank you!

[MAwwad]

You can create a measure to calculate the percentage of compliant clients within a quarter based on the distinct count of Client ID#:

Compliant Percentage = VAR TotalClients = DISTINCTCOUNT('Table 3'[Client ID#]) VAR CompliantClients = CALCULATE(COUNTROWS('Table 3'), 'Table 3'[Condition] = "compliant") RETURN IF(TotalClients > 0, DIVIDE(CompliantClients, TotalClients), BLANK())

This measure uses the DISTINCTCOUNT function to get the total number of distinct clients within a quarter. Then, it uses the CALCULATE function with the COUNTROWS function to count the number of compliant encounters within a quarter. Finally, it divides the number of compliant encounters by the total number of distinct clients within the same quarter, and returns the result as a percentage.

You can use this measure in a visual or a table to show the compliant percentage by quarter.

[Anonymous]

Thank you for the quick response but I don't believe this worked to correctly remove duplicate Client IDs.  Any other thoughts?

[Anonymous · Accepted solution]

I believe it is because the same client may have multiple "compliant" conditions within the same quarter.  Is there a way to also do a distinct count of the client ID in the portion that counts the condition?