Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the follow measure which give me a Contacts Per Hour (CPH) figure for an individual agent on an individual day however when expanding the criteria to multiple days or agents this calculation does not work.
CPH = SUM ( NonVoiceByProduct[TotalInteractions] ) / ( ( SUM ( NonVoiceByProduct[OnlineHours] ) / ( COUNT ( NonVoiceByProduct[OnlineHours] ) / DISTINCTCOUNT ( NonVoiceByProduct[OnlineHours] ) ) ) )
So for Agent One on the 05/11/2018 (UK Date Format). The calculation would be;
52 / ( 35 / ( 5 / 1 ) ) = 7.43
However expanding this across multiple days or agents does not work so obviously the maths is incorrect. I would like a measure to produce the correct Contacts Per Hour regardless of the criteria selected. The calculation I am trying to acheive is;
TotalInteractions / OnlineHours
Calculating the total OnlineHours is what I am finding difficult as the OnlineHours value is repeated for each source and skill. It is important that these are kept in any result as I would like to be able to slice by skill to get a CPH by Skill.
The values I would expect for the applied criteria (in italics) follow;
Date = 05/11/2018
Agents = All
TotalInteractions = 110
OnlineHours = 14 (Two agents working 7 hours each)
CPH = 7.85
Date = All
Agents = Agent One
TotalInteractions = 104
OnlineHours = 17
CPH = 6.11
Date = All
Agents = Agent Two
TotalInteractions = 114
OnlineHours = 14
CPH = 8.14
An example of the data follows;
Date | AgentName | InteractionSource | SkillName | TotalInteractions | OnlineHours | AgentId |
05/11/2018 | Agent One | Web Chat | Skill1 | 40 | 7 | 98765432 |
05/11/2018 | Agent Two | Web Chat | Skill1 | 16 | 7 | 12345678 |
05/11/2018 | Agent Two | Skill1 | 17 | 7 | 12345678 | |
02/11/2018 | Agent Two | Web Chat | Skill1 | 14 | 7 | 12345678 |
02/11/2018 | Agent Two | Skill1 | 16 | 7 | 12345678 | |
05/11/2018 | Agent One | Web Chat | Skill2 | 2 | 7 | 98765432 |
05/11/2018 | Agent One | Web Chat | Skill2 | 2 | 7 | 98765432 |
05/11/2018 | Agent One | Web Chat | Skill2 | 2 | 7 | 98765432 |
05/11/2018 | Agent Two | Web Chat | Skill2 | 2 | 7 | 12345678 |
05/11/2018 | Agent Two | Web Chat | Skill2 | 6 | 7 | 12345678 |
05/11/2018 | Agent Two | Web Chat | Skill2 | 7 | 7 | 12345678 |
05/11/2018 | Agent Two | Skill2 | 3 | 7 | 12345678 | |
02/11/2018 | Agent Two | Web Chat | Skill2 | 6 | 7 | 12345678 |
02/11/2018 | Agent Two | Skill2 | 4 | 7 | 12345678 | |
02/11/2018 | Agent Two | Skill2 | 2 | 7 | 12345678 | |
02/11/2018 | Agent Two | Skill2 | 3 | 7 | 12345678 | |
05/11/2018 | Agent One | Web Chat | Skill3 | 6 | 7 | 98765432 |
05/11/2018 | Agent Two | Skill3 | 3 | 7 | 12345678 | |
02/11/2018 | Agent Two | Skill3 | 9 | 7 | 12345678 | |
05/11/2018 | Agent Two | Web Chat | Skill4 | 1 | 7 | 12345678 |
02/11/2018 | Agent Two | Web Chat | Skill3 | 1 | 7 | 12345678 |
05/11/2018 | Agent Two | Skill2 | 1 | 7 | 12345678 | |
05/11/2018 | Agent Two | Skill2 | 1 | 7 | 12345678 | |
05/11/2018 | Agent Two | Skill2 | 1 | 7 | 12345678 | |
02/11/2018 | Agent Two | Web Chat | Skill2 | 1 | 7 | 12345678 |
02/11/2018 | Agent One | Skill2 | 1 | 10 | 98765432 | |
02/11/2018 | Agent One | Web Chat | Skill2 | 1 | 10 | 98765432 |
02/11/2018 | Agent One | Web Chat | Skill2 | 2 | 10 | 98765432 |
02/11/2018 | Agent One | Web Chat | Skill2 | 3 | 10 | 98765432 |
02/11/2018 | Agent One | Web Chat | Skill2 | 3 | 10 | 98765432 |
02/11/2018 | Agent One | Web Chat | Skill3 | 4 | 10 | 98765432 |
02/11/2018 | Agent One | Skill1 | 16 | 10 | 98765432 | |
02/11/2018 | Agent One | Web Chat | Skill1 | 22 | 10 | 98765432 |
Solved! Go to Solution.
I would suggest using an AVERAGEX over a SUMMARIZE. Since your values are repeated by skill it will be necessary to introduce an average, or possibly MAX or MIN.
I think what you want is:
CPH = VAR __interactions = SUM([TotalInteractions]) VAR __onlineHours = SUMX(SUMMARIZE('Table14',[Date],[AgentName],[InteractionSource],[SkillName],"__onlineHours",AVERAGE([OnlineHours])),[__onlineHours]) RETURN DIVIDE(__interactions,__onlineHours)
See page 8 of attached file.
Hi @mark_carlisle,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @mark_carlisle,
Based on my test, you could refer to below measure:
Measure = SUM(Table1[TotalInteractions])/ (CALCULATE(MAX(Table1[OnlineHours]),ALLSELECTED('Table1'[AgentName]))+CALCULATE(MIN(Table1[OnlineHours]),ALLSELECTED('Table1'[Date])))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
I think what you want is:
CPH = VAR __interactions = SUM([TotalInteractions]) VAR __onlineHours = SUMX(SUMMARIZE('Table14',[Date],[AgentName],[InteractionSource],[SkillName],"__onlineHours",AVERAGE([OnlineHours])),[__onlineHours]) RETURN DIVIDE(__interactions,__onlineHours)
See page 8 of attached file.
I would suggest using an AVERAGEX over a SUMMARIZE. Since your values are repeated by skill it will be necessary to introduce an average, or possibly MAX or MIN.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |