Forum Discussion
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 =
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.
| 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 |
2 Replies
Anonymous , Assume Hour Decimal is a measure
then create a new measure like
Time(0,0,0) + [Hour Decimal]/24
or refer
Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389amitchandak
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:
