Calculate Contacts Per Hour / Sum Distinct values

[mark_carlisle]

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	Email	Skill1	17	7	12345678
02/11/2018	Agent Two	Web Chat	Skill1	14	7	12345678
02/11/2018	Agent Two	Email	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	Email	Skill2	3	7	12345678
02/11/2018	Agent Two	Web Chat	Skill2	6	7	12345678
02/11/2018	Agent Two	Email	Skill2	4	7	12345678
02/11/2018	Agent Two	Email	Skill2	2	7	12345678
02/11/2018	Agent Two	Email	Skill2	3	7	12345678
05/11/2018	Agent One	Web Chat	Skill3	6	7	98765432
05/11/2018	Agent Two	Email	Skill3	3	7	12345678
02/11/2018	Agent Two	Email	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	Email	Skill2	1	7	12345678
05/11/2018	Agent Two	Email	Skill2	1	7	12345678
05/11/2018	Agent Two	Email	Skill2	1	7	12345678
02/11/2018	Agent Two	Web Chat	Skill2	1	7	12345678
02/11/2018	Agent One	Email	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	Email	Skill1	16	10	98765432
02/11/2018	Agent One	Web Chat	Skill1	22	10	98765432

[Greg_Deckler · Accepted 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.

[v-danhe-msft]

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 

[v-danhe-msft]

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

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler · Accepted 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.