Forum Discussion
Solved
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?
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
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)SergeiBaklan 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?
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
