Forum Discussion
Trying to Calculate How Many Employees Worked on Multiple Customers in Given Time Period
- 3 years ago
Thank you for the quick response Greg_Deckler!
This is giving me 98% across the board for every week (which I would expect to see around 30%). Something I accidentally omitted in my example data was that customer numbers will often repeat, and I can only count each unique customer once, sorry for the confusion!
- 3 years ago
Spudder112 Well that certainly makes a difference but should be an easy fix. PBIX is attached with ammended data to demonstrate a duplicate.
Employees Working More Than 1 Customer Measure = VAR __NumEmployees = COUNTROWS(DISTINCT('Table'[Employee ID])) VAR __Table = SUMMARIZE('Table',[Date],[Employee ID],"__Count",COUNTROWS(DISTINCT(SELECTCOLUMNS('Table',"__Customer",[Customer])))) VAR __Result = DIVIDE( COUNTROWS(FILTER(__Table, [__Count] > 1)), __NumEmployees) + 0 RETURN __Result
Thank you for the quick response Greg_Deckler!
This is giving me 98% across the board for every week (which I would expect to see around 30%). Something I accidentally omitted in my example data was that customer numbers will often repeat, and I can only count each unique customer once, sorry for the confusion!
Spudder112 Well that certainly makes a difference but should be an easy fix. PBIX is attached with ammended data to demonstrate a duplicate.
Employees Working More Than 1 Customer Measure =
VAR __NumEmployees = COUNTROWS(DISTINCT('Table'[Employee ID]))
VAR __Table = SUMMARIZE('Table',[Date],[Employee ID],"__Count",COUNTROWS(DISTINCT(SELECTCOLUMNS('Table',"__Customer",[Customer]))))
VAR __Result = DIVIDE( COUNTROWS(FILTER(__Table, [__Count] > 1)), __NumEmployees) + 0
RETURN
__Result
- Spudder1123 years agoHelper I
Thank you very much! I will use this logic in the future a ton.