SOLVED

Getting the average number of working days for users

New Contributor

Hi, I'd like to know if there's a solution to an issue I'm experiencing with Power Pivot / DAX. 

 

I have a table with calls handled by each member of a team. By doing a distinct count of the dates of the call, I'm able to determine how many days per month, quarter, year, each team member handled calls. 

 

However, I would like to compare these distinct counts with an average for all team members. 

 

Does anybody have any idea how I could do this?

5 Replies

@Benji88 

It depends on how to calculate an average for the period. Or you know team size and take it for the period from separate table; or you calculate it from the table with calls. If the latest it could be different from month to month, even of nobody hired or fired. People could be in vacation, in sick leave, perhaps something else.

 

Anyway , measures could be like

Number of Dates on Call:=DISTINCTCOUNT( Calls[Date] )

Average Dates on Call per Person:=CALCULATE(
   DIVIDE( DISTINCTCOUNT(Calls[Date]),
           DISTINCTCOUNT(Calls[Name]), 0),
   ALL(Calls[Name] ))

Dates on Call to Average, %:=DIVIDE(
    [Number of Dates on Call],
    [Average Dates on Call per Person], 0)

@Sergei Baklan thanks a lot for your input. I tried your measures but I don't get the desired result. I added a copy of the data I'm working with. I have employees A to N. In February (not counting a full month), the average of days worked (i.e. where employees took calls) should be 4,23. For March, the average should be 8,14 days, etc. Any ideas how I could get that result, using measures?

best response confirmed by Benji88 (New Contributor)
Solution

@Benji88 

Sorry, I misunderstood the logic. Please try

Average days:=VAR daysPerPerson =
    SUMMARIZE ( Calls2, Calls2[IRF_START_DATE], Calls2[RESOURCE_NAME] )
VAR teamDays =
    COUNTROWS ( daysPerPerson )
VAR team =
    DISTINCTCOUNT ( Calls2[RESOURCE_NAME] )
RETURN
    DIVIDE ( teamDays, team, 0 )

/////////
Number of days:=DISTINCTCOUNT ( Calls2[IRF_START_DATE] )

////////
Days on Call to Average, %:=VAR averageForTeam =
    CALCULATE ( [Average days], ALL ( Calls2[RESOURCE_NAME] ) )
RETURN
    DIVIDE ( [Number of days], averageForTeam, 0 )

in attached file

This works great, thanks a lot for your help!

@Benji88 , you are welcome