Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Spudder112
Helper I
Helper I

Trying 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%

 

2 ACCEPTED SOLUTIONS

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!

View solution in original post

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you very much! I will use this logic in the future a ton.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.