Jun 03 2022 04:24 AM
Jun 03 2022 04:24 AM
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?
Jun 03 2022 10:33 AM
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)
Jun 06 2022 01:08 AM
@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?
Jun 06 2022 02:42 AMSolution
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