Calculate Agent Utilization

[prasadhebbar315]

Hello everyone,

I need your help in calculating agent utilization based on number of inbound & outbound calls, AHT(s), Avg Hold Time(s) and Avg Wrap Time (s). In the below table,

• Total calls taken = Total Calls Accepted + Total Outbound
• Calls per day = Total calls taken/# of days in a month by agent
• Total Productive time = Calls per day*(AHT(s) + Avg Hold Time(s) + Avg Wrap Time(s))
• Utilzation = Total Productive time/ (480*60)

The ask is to get utilzation for each agents by each month.

Sample output expected for Agent Name: AABIDIN for Month=2

• Total calls taken = (22+16+13+18) + (2+3+7+9) = 90
• Calls per day = 90/4 (4 days in month 2)=23
• Total Productive time = 23*(average(582,948,801,684)+average(73,168,107,103)+average(101,243,189,183))=23524
• Utilzation = 23524/ (480*60) = 81.7%

The below calculation should work for each agents by year and month

Year	Month	Agent Name	Total Calls Accepted	AHT (s)	Avg Hold Time (s)	Avg Wrap Time (s)	Total Outbound
2022	2	AABIDIN	22	582	73	101	2
2022	2	AABIDIN	16	948	168	243	3
2022	2	AABIDIN	13	801	107	189	7
2022	2	AABIDIN	18	684	103	183	9
2022	3	AABIDIN	14	901	82	268	13
2022	3	AABIDIN	23	711	112	180	4
2022	3	AABIDIN	19	839	99	261	5
2022	3	AABIDIN	16	942	158	248	28
2023	1	AADAMCIK	2	3741	0	3268	7
2023	1	AADAMCIK	2	963	0	505	1
2023	1	AADAMCIK	5	524	0	93	11
2023	1	AADAMCIK			0	0	3
2023	2	AADAMCIK	7	475	0	82	9
2023	2	AADAMCIK	6	502	0	135	0
2023	2	AADAMCIK	2	398	0	41	1

[Anonymous · Accepted solution]

HI @prasadhebbar315,

You can check the following sample and measure formulas if they suitable for your requirement:

Total calls taken = 
CALCULATE (
    SUM ( 'Table'[Total Calls Accepted] ) + SUM ( 'Table'[Total Outbound] ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[Year] ),
    VALUES ( 'Table'[Month] ),
    VALUES ( 'Table'[Agent Name] )
)

Calls per day = 
VAR dayCountPerAgent =
    CALCULATE (
        COUNT ( 'Table'[Agent Name] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Year] ),
        VALUES ( 'Table'[Month] ),
        VALUES ( 'Table'[Agent Name] )
    )
RETURN
    [Total calls taken] / dayCountPerAgent

Total Productive time = 
[Calls per day]
    * CALCULATE (
        AVERAGE ( 'Table'[AHT (s)] ) + AVERAGE ( 'Table'[Avg Hold Time (s)] )
            + AVERAGE ( 'Table'[Avg Wrap Time (s)] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Year] ),
        VALUES ( 'Table'[Month] ),
        VALUES ( 'Table'[Agent Name] )
    )

Utilzation = 
VAR rate = 480 * 60
RETURN
    [Total Productive time] / rate

Regards,

Xiaoxin Sheng

[prasadhebbar315]

@Anonymous Thanks for providing the solution. It did work on my actual data.