How to calculate Average Queue Time

[Anonymous]

 How to calculate Average Queue Time

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the solution

Average Queue Time = 
VAR TotalTime = [Queue Time]
VAR TotalSeconds = SECOND ( TotalTime ) + MINUTE ( TotalTime ) * 60 + HOUR ( TotalTime ) * 3600 
VAR AverageSeconds = DIVIDE ( TotalSeconds, SUM ( 'Table'[Calls Queued] ), 0 )
VAR Hours = FORMAT ( QUOTIENT ( AverageSeconds, 3600 ), "00" )
VAR BalanceSeconds = MOD ( AverageSeconds, 3600 )
VAR Minutes = FORMAT ( QUOTIENT ( BalanceSeconds, 60 ), "00" )
VAR Seconds = FORMAT ( MOD ( BalanceSeconds, 60 ), "00" )
VAR Result = Hours & ":" & Minutes & ":" & Seconds
RETURN 
    Result

[Anonymous]

Hi ,

I was shared sample data with you, DAX was working fine , but when i am adding 2 months data it is showing wrong, Queue time is showing wrong , it will go >24 hours , for < 24 hr it is working fine.

for > 24 hour queue time what is the solution for that..

[tamerj1]

Hi @Anonymous 
If you add the month column to the visual it should work fine. 

[Anonymous]

 For interval wise it is showing correct but total is showing incorrect,,

[Anonymous]

can you please check ?

[komald]

@Anonymous , Try this

create measure,
avg queue time=divide(sum(total queue time) , sum(calls queued))
put this measure in card

or

create calculated column
avg queue time= total queue time / queued calls

[Anonymous]

Hi,

Total queue time is in text format so unable to divide with call queued ..

[komald]

@Anonymous , then use value function

avg queue time= value( total queue time )/ value(queued calls)
try this let me know is this work for u or not.

[Anonymous]

Hi,

It's not working ..

[komald]

@Anonymous , can you please share your dax with error?

[Anonymous]

VALUE(SUM('Raw Data'[Total Queue Time])) / SUM('Raw Data'[Calls Queued]))

[komald]

@Anonymous , 

[Anonymous]

let me check

[komald]

@Anonymous , if i ans your question please mark as a solution. Thanks

[Anonymous]

Hi,

Still it's not done , i am sharing some more raw data for more clarity.. can you please check ..

left side is raw data and right side table is output .. i wanted to show same output table..

can you please help me out..

thanks in advanced..

[Anonymous]

@tamerj1 ,can you please solve this..

[Anonymous]

Raw data

Date	Interval	Calls Queued	Total Queue Time
02-05-2022	02:00-02:30	0	00:00:00
02-05-2022	02:00-02:30	0	00:00:00
02-05-2022	02:00-02:30	0	00:00:00
02-05-2022	02:30-02:60	2	00:01:43
02-05-2022	02:30-02:60	0	00:00:00
02-05-2022	02:30-02:60	1	00:00:43
02-05-2022	02:30-02:60	1	00:02:42
02-05-2022	02:30-02:60	1	00:07:43
02-05-2022	02:30-02:60	0	00:00:00
02-05-2022	02:30-02:60	2	00:02:10
02-05-2022	02:30-02:60	2	00:02:29
02-05-2022	02:30-02:60	0	00:00:00
02-05-2022	05:00-05:30	1	00:05:24
02-05-2022	05:00-05:30	0	00:00:00
02-05-2022	05:00-05:30	2	00:05:55
02-05-2022	05:00-05:30	1	00:00:43
02-05-2022	05:00-05:30	0	00:00:00
02-05-2022	05:00-05:30	1	00:01:36
02-05-2022	05:00-05:30	1	00:00:09
02-05-2022	05:00-05:30	1	00:00:22
02-05-2022	05:30-05:60	1	00:18:25
02-05-2022	05:30-05:60	2	00:09:18
02-05-2022	05:30-05:60	0	00:00:00
02-05-2022	05:30-05:60	1	00:02:53
02-05-2022	05:30-05:60	2	00:07:56
02-05-2022	05:30-05:60	1	00:04:22
02-05-2022	05:30-05:60	1	00:01:01
02-05-2022	05:30-05:60	1	00:02:52
02-05-2022	05:30-05:60	1	00:07:21

Result
Interval	Calls Queued	Total Queue Time	Average Queue Time(Total queued / call queued)
Total	26	1:25:47	0:03:18
02:00-02:30	0	0:00:00	0:00:00
02:30-02:60	9	0:17:30	0:01:57
05:00-05:30	7	0:14:09	0:02:01
05:30-05:60	10	0:54:08	0:05:25

[Anonymous]

@amitchandak, can you help me out for same