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...
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
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
- This works great, thanks a lot for your help!
Benji88 , you are welcome