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