Forum Discussion

Spudder112's avatar
Spudder112
Helper I
3 years ago
Solved

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 ...
  • Spudder112's avatar
    Spudder112
    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!

  • Greg_Deckler's avatar
    Greg_Deckler
    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