Displaying "average time" in visuals

Anonymous — Wed, 08 Mar 2023 14:31:37 GMT

Hey all,

I have a report that is published daily and I would like to see the "average" of the time it is reported during different periods. Basically I would like to have a time visual with the time the report is sent out on every day, plus a gauge/KPI measure with the average time it is sent out on the selected month. I can't seem to get it working properly. I now have two semi-solutions, but cannot have them in one.

I made a measure
Average Time =

Format(

AVERAGE('PRIME'[Time])

,"HH:MM")

This helped me to see the average time that the report is sent out in a table. However, because this is "Time" type data, I can't get it in a visual. What I can get in a visual is a "Decimal hour" data. In Power Query I made an extra column that basically transforms the time into a decimal (Hence, making 10:30:00 into 10.5). This allowed me to put the time in a visual, such as a KPI measure. However, the way it is displayed is a bit ugly, as it shows that the average time the report was sent out is 10.75, rather than 10:45. I tried the Format DAX measure on the "Decimal hour" column, but then I have the same issue again as before that I can't display it in a visual.

See below the data:

Subject	Date	Time	Hour	Minute	Hour Decimal
Report 06 March 2023	08/03/2023	10:26:21	10	26	10,43333333
Report 03 March 2023	07/03/2023	13:36:20	13	36	13,6
Report 02 March 2023	06/03/2023	11:16:48	11	16	11,26666667
Report 01 March 2023	03/03/2023	09:57:43	9	57	9,95
Report 28 February 2023	02/03/2023	15:42:29	15	42	15,7
Report 27 February 2023	01/03/2023	13:38:48	13	38	13,63333333
Report 24 February 2023	28/02/2023	10:30:04	10	30	10,5
Report 23 February 2023	27/02/2023	09:32:23	9	32	9,53333333
Report 22 February 2023	24/02/2023	13:49:46	13	49	13,81666667
Report 21 February 2023	23/02/2023	10:52:10	10	52	10,86666667
Report 20 February 2023	22/02/2023	14:26:17	14	26	14,43333333
Report 17 February 2023	21/02/2023	11:10:03	11	10	11,16666667
Report 16 February 2023	20/02/2023	12:25:33	12	25	12,41666667
Report 15 February 2023	17/02/2023	14:01:36	14	1	14,01666667
Report 14 February 2023	16/02/2023	16:15:52	16	15	16,25
Report 13 February 2023	15/02/2023	10:53:50	10	53	10,88333333
Report 10 February 2023	14/02/2023	09:32:52	9	32	9,53333333
Report 09 February 2023	13/02/2023	09:38:10	9	38	9,63333333
Report 08 February 2023	10/02/2023	09:31:08	9	31	9,51666667
Report 07 February 2023	09/02/2023	11:58:55	11	58	11,96666667
Report 06 February 2023	08/02/2023	10:42:16	10	42	10,7
Report 03 February 2023	07/02/2023	09:58:36	9	58	9,96666667
Report 02 February 2023	06/02/2023	16:18:20	16	18	16,3
Report 01 February 2023	03/02/2023	10:19:02	10	19	10,31666667
Report 31 January 2023	02/02/2023	14:03:17	14	3	14,05

Re: Displaying "average time" in visuals

Anonymous — Thu, 09 Mar 2023 09:14:08 GMT

amitchandak
If I try this, I do get an average Time variable again, but I run into the same issue that I can't then display this measure in a visual such as a KPI measure, only as a Card. However, I would like to see this evolving over time. With the time, I can make a nice Card saying "Average time last month is 10:55", but I would also like to see this evolving over time and then the measure can't be selected. Only the "Hour Decimal" can be used but then it does'nt make sense:

topic Re: Displaying "average time" in visuals in DAX Commands and Tips

Displaying "average time" in visuals

Re: Displaying "average time" in visuals

Re: Displaying "average time" in visuals