SOLVED

# Getting the average number of working days for users

Copper Contributor

# Getting the average number of working days for users

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

# Re: Getting the average number of working days for users

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)``````

# Re: Getting the average number of working days for users

@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 (Copper Contributor)
Solution

# Re: Getting the average number of working days for users

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

# Re: Getting the average number of working days for users

This works great, thanks a lot for your help!

# Re: Getting the average number of working days for users

@Benji88 , you are welcome

1 best response

Accepted Solutions
best response confirmed by Benji88 (Copper Contributor)
Solution

# Re: Getting the average number of working days for users

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