Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%
Solved! Go to Solution.
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
@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
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
Thank you very much! I will use this logic in the future a ton.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |