Forum Discussion

gambleave's avatar
gambleave
Helper II
4 years ago
Solved

Counting Distinct Combinations

Hi All, 

For a scenario of consultants at a business providing service to clients, I have the following simple measures to count the number of distinct clients (individuals at companies) to receive high touch service within a time period, as well as the number of distinct consultants providing high touch service during the period. I use this in a matrix with companies in rows and months as columns.  

 

Distinct Clients High Touch = 
CALCULATE(DISTINCTCOUNT(ServiceHistory[Client Name]), ServiceHistory[Service Type]=“High Touch”)
 
Distinct Consultants High Touch =
CALCULATE(DISTINCTCOUNT(ServiceHistory[Employee Name]), ServiceHistory[Service Type]=“High Touch”)

 

I want to create a measure to count the total across variations of consultants/clients (e.g. if consultant #1 services 4 clients, consultant #2 services 6 clients, consultant #3 services 5 clients, the total would be 15 at that company). I tried the following measure, but the values are too high. What have I got wrong? 


ConsultantClient Combinations =
SUMX(SUMMARIZE(ServiceHistory, ServiceHistory[Client Name], ServiceHistory[Employee Name]), CALCULATE(DISTINCTCOUNT(ServiceHistory[Activity ID]), ServiceHistory[Service Type]=“High Touch”))

 

Activity ID here is a unique identifier for interactions (although there could be duplication, e.g. if a consultant meets with 3 clients as a group, there would be three records with the same Activity ID). 

 

Thank you!

 

3 Replies