Average Time is adding to together instead of showing average for time frame.

[inglexjc]

I have a report that get's added to daily from our phones reports. The data comes in as an excel spreadsheet for the day before.  The data then goes into our Power BI dashboard.  The report shows the Average Queue Time for EACH call type.  When I go day by day the average shows correctly.  But if I want to know the average queue time for dates 9/3/2024 to 9/30/2024 the dashboard shows Average of 6:01:01 when the actual average for that time frame is 00:01:01

The data comes in as a decimal number and I've tried changing Summarization from "Sum" to "Average" and "Don't Summarize" but none of that fixes the Average showing.

I also need to keep the format in the dispaly as "HH:MM:SS".

[inglexjc]

It looks like the Average is not working only for month of Sept.  October is working correctly.  Not sure what the issue is with Sept but I'm moving on since October is working corectly.

Thank you.

[az38]

hi @inglexjc 

I assume in this Top Ribbon you change default behaviour and not configured at the visual level 

Try to change it in Fields of Build Pane

[inglexjc]

Since the card is using a measure to get the correct formating there is not an option to change the configuration:

[lbendlin]

Is the measure averaging? Show the DAX .

[inglexjc]

Yes.  

Average Queue Time = FORMAT(AVERAGE(Query4[Avg Queue Time]), "HH:MM:SS")

And after I changed it to what you advised "hh:nn:ss":

Average Queue Time = FORMAT(AVERAGE(Query4[Avg Queue Time]), "hh:nn:ss")

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[inglexjc]

@lbendlin 

The data comes in each day and builds up into a folder so Power BI accounts for each day:

In the screen shot originally you will see that the aveage queue time is showing as 6:01:01 and it should be 01:01.

[lbendlin]

Not what I see

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrAyACIzpVgdMNvYygiJbWgOYxtZGRjC2AZWhnC2oZWRqVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Avg Queue Time" = _t]),
    #"Changed Type" = List.Average(Table.TransformColumnTypes(Source,{{"Avg Queue Time", type duration}})[Avg Queue Time])
in
    #"Changed Type"

[inglexjc]

ok let me give you ALL the data that I'm using in my example.  Average should be  0:01:00 but PowerBI Card is showing Average Queue Time as 4:01:00.  It's adding 4 hours.

CSQ Name	Calls Presented	Calls Handled	Calls Abandoned	Percent Handled	Average Handle Time	Average Queue Time	Max Queue Time	Average Abandoned Time	Avg Abandoned Per Day	Date
HS_ChildSupport	58	52	6	89.66	0:04:40	0:01:33	0:39:23	0:01:27	3	9/4/2024
HS_ChildSupport	42	37	5	88.1	0:04:26	0:00:33	0:04:23	0:00:36	2.5	9/5/2024
HS_ChildSupport	67	57	10	85.07	0:04:09	0:01:18	0:07:59	0:01:26	5	9/6/2024
HS_ChildSupport	60	60	0	100	0:04:26	0:00:24	0:06:01	0:00:00	0	9/9/2024
HS_ChildSupport	53	53	0	100	0:04:56	0:00:23	0:02:55	0:00:00	0	9/11/2024
HS_ChildSupport	64	62	2	96.88	0:03:29	0:01:00	0:06:09	0:03:36	1	9/12/2024
HS_ChildSupport	52	47	5	90.38	0:04:42	0:01:09	0:07:01	0:02:08	2.5	9/13/2024
HS_ChildSupport	63	60	3	95.24	0:04:20	0:00:47	0:09:06	0:02:05	1.5	9/16/2024
HS_ChildSupport	45	44	1	97.78	0:04:20	0:00:43	0:06:07	0:00:50	0.5	9/17/2024
HS_ChildSupport	44	37	7	84.09	0:05:18	0:00:51	0:06:51	0:00:54	3.5	9/18/2024
HS_ChildSupport	42	41	1	97.62	0:04:11	0:01:03	0:08:16	0:01:39	0.5	9/19/2024
HS_ChildSupport	33	29	4	87.88	0:05:18	0:01:31	0:07:01	0:02:33	2	9/20/2024
HS_ChildSupport	63	56	7	88.89	0:04:48	0:00:53	0:11:09	0:02:15	3.5	9/23/2024
HS_ChildSupport	78	69	9	88.46	0:04:47	0:00:57	0:07:29	0:01:36	4.5	9/24/2024
HS_ChildSupport	55	50	5	90.91	0:04:42	0:00:42	0:07:49	0:01:37	2.5	9/25/2024
HS_ChildSupport	57	54	3	94.74	0:04:24	0:01:52	0:23:45	0:11:21	1.5	9/26/2024
HS_ChildSupport	38	38	0	100	0:04:37	0:00:35	0:07:25	0:00:00	0	9/27/2024
HS_ChildSupport	62	58	4	93.55	0:04:33	0:01:41	0:10:45	0:01:13	2	9/30/2024

[lbendlin · Accepted solution]

This will fail if the average queue time ever goes over 24 hours.

[inglexjc]

I notice in your attachment power bi desktop that the data format for Avg Queue Time you are able to change format to "hh:nn:ss".  I don't have this option.  For my card I was already using the format measure to "HH:MM:SS" and that's how I'm able to get the correct format but it's not coming up with the right number. I also tried "hh:nn:ss" and I get the same result.

[lbendlin]

MM is for months, not for minutes.

[inglexjc]

I did try "hh:nn:ss" and it's still not right:

[lbendlin]

Are you averaging or summing?

[inglexjc]

The data comes in with the column averge queue time.  It automatically shows summaricaiton as SUM: 

When I change that to AVERAGE nothing changes:

I've also tried Don't summarize and nothing changes:

[inglexjc]

@lbendlin 

Here is the excel data that comes in based on the previous day calls:

CSQ Name	Service Level (sec)	Calls Presented	Calls Handled	Percent Handled	Avg Handle Time	Max Handle Time	Calls Abandoned	Percent Abandoned	Avg Abandoned Time	Max Abandoned Time	Calls Dequeued	Percent Dequeued	Avg Dequeued Time	Max Dequeued Time	Avg Speed of Answer	Calls Handled < Service Level	Calls Abandoned < Service Level	Avg Abandoned Per Day	Max Abandoned Per Day	Calls Handled by Other	Avg Queue Time	Max Queue Time
HS_AAA	5	16	16	100	0:02:14	0:13:04	0	0	0:00:00	0:00:00	0	0	0:00:00	0:00:00	0:00:06	12	0	0	0	0	0:00:06	0:00:25
HS_AP_Support_Eng	5	71	56	78.87	0:05:18	0:15:55	15	21.13	0:02:18	0:07:53	0	0	0:00:00	0:00:00	0:03:44	17	2	7.5	15	0	0:03:26	0:23:58
HS_Adult_Protection	5	5	1	20	0:18:18	0:18:18	4	80	0:01:28	0:05:52	0	0	0:00:00	0:00:00	0:10:33	0	3	2	4	0	0:03:17	0:10:33
HS_CCAP	5	26	22	84.62	0:04:17	0:09:51	4	15.38	0:07:53	0:14:46	0	0	0:00:00	0:00:00	0:00:57	0	0	2	4	0	0:02:01	0:14:46
HS_CMA_Eng	5	11	11	100	0:03:00	0:11:47	0	0	0:00:00	0:00:00	0	0	0:00:00	0:00:00	0:00:11	1	0	0	0	0	0:00:11	0:00:30
HS_ChildSupport	5	66	59	89.39	0:05:32	0:12:04	7	10.61	0:02:47	0:06:30	0	0	0:00:00	0:00:00	0:01:15	4	0	3.5	7	0	0:01:25	0:06:30