Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Olaf18
Frequent Visitor

Displaying "average time" in visuals

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:
 
SubjectDateTimeHourMinuteHour Decimal
Report 06 March 202308/03/202310:26:21102610,43333333
Report 03 March 202307/03/202313:36:20133613,6
Report 02 March 202306/03/202311:16:48111611,26666667
Report 01 March 202303/03/202309:57:439579,95
Report 28 February 202302/03/202315:42:29154215,7
Report 27 February 202301/03/202313:38:48133813,63333333
Report 24 February 202328/02/202310:30:04103010,5
Report 23 February 202327/02/202309:32:239329,53333333
Report 22 February 202324/02/202313:49:46134913,81666667
Report 21 February 202323/02/202310:52:10105210,86666667
Report 20 February 202322/02/202314:26:17142614,43333333
Report 17 February 202321/02/202311:10:03111011,16666667
Report 16 February 202320/02/202312:25:33122512,41666667
Report 15 February 202317/02/202314:01:3614114,01666667
Report 14 February 202316/02/202316:15:52161516,25
Report 13 February 202315/02/202310:53:50105310,88333333
Report 10 February 202314/02/202309:32:529329,53333333
Report 09 February 202313/02/202309:38:109389,63333333
Report 08 February 202310/02/202309:31:089319,51666667
Report 07 February 202309/02/202311:58:55115811,96666667
Report 06 February 202308/02/202310:42:16104210,7
Report 03 February 202307/02/202309:58:369589,96666667
Report 02 February 202306/02/202316:18:20161816,3
Report 01 February 202303/02/202310:19:02101910,31666667
Report 31 January 202302/02/202314:03:1714314,05

 

Olaf18_0-1678280422772.png

 

2 REPLIES 2

@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: 

Olaf18_0-1678353177823.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors