Forum Discussion
Helper ITrying to Calculate How Many Employees Worked on Multiple Customers in Given Time Period
I had asked a similar question, but decided to take a different route after re-working some data...
Table 1
Order ID
Order Date
Order Amount
Employee ID
Customer ID
I want to figure out how many Employee worked on a different customer idea on each date.
Example data:
Employee ID Employee Name Customer Date
1212 Jon 5133 1/1/2023
2455 Sam 1344 1/2/2023
2455 Sara 11111 1/2/2023
9559 Charles 3553 1/1/2023
2455 Sam 4949 1/1/2023
2455 Sam 1344 1/1/2023
Finished product:
Date: 1/1/2023 1/2/2023
Employees working more than 1 customer 33% 0%
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
4 Replies
- Greg_Deckler
Community Champion
Spudder112 Your sample data won't give you the results you expect because you have duplicate ID's for Sam and Sara but if you correct that this will work:
Employees Working More Than 1 Customer Measure = VAR __NumEmployees = COUNTROWS(DISTINCT('Table'[Employee ID])) VAR __Table = SUMMARIZE('Table',[Date],[Employee ID],"__Count",COUNTROWS('Table')) VAR __Result = DIVIDE( COUNTROWS(FILTER(__Table, [__Count] > 1)), __NumEmployees) + 0 RETURN __Result
Spudder112Thank 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!
- Greg_Deckler
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