How to calculate the average of a time column (HH:MM:SS)?

[Anonymous]

Hi, folks!

I need to calculate the average of a time column (HH:MM:ss) and then show this in a line graphic in the same format (HH:MM:ss).

So what I did so far was to make my time column become decimal number, for example,

col 1            col2

01:00:00        1

02:00:00        2

That's the DAX that I used to generate column2 ->>  col2 = 24, *(col1) 

And then I can calculate AVG(col2), but I need to plot on my graphich in the time format (hh:mm:ss)

Can anybody assist me on this, please?

Thank you!

[Anonymous]

I have calculated the Average time and now I wanted to plot that in a visual. Is that possible anywhere in PowerBI? What type of graph have you guys built using time as a measure? My challenge has been trying to plot something using time without transforming it into a sum of seconds. For example, 3 min instead of 180 seconds. Users have trouble making the calculation.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Y-axis only supports numeric fields. If you add a text field like (HH:MM:ss) onto it, it will be aggreated by default. It is not available to show time on Y-axis with format HH:MM:ss.

Regards,

Yuliana Gu

[Anonymous]

Ok, thanks! Is there other way I can plot then? Any tips?

[TomMartens]

Hey,

maybe you will find this article helpful, it's about handling of time or better durations and how to treat duration in DAX:

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

After you converted your time to a decimal number that represents a duration (consider a meaningful unit), then you can use all the DAX functions to aggregate numbers

Hope this will gives you some ideas

Regards

Tom

[gooranga1]

You could create a measure below;

Average = FORMAT(CALCULATE(AVERAGE(Table[col1])),"HH:mm:ss")

[SANTOSHVAIDYA]

works perfectly......thanks a ton.....

[Anonymous]

Didn't work. When I put the measure into the Value box of the line grapich, it doesn't stay.

But thanks anyways @gooranga1

[gooranga1]

ah @Anonymous yeah that is a downer, you can't chart times at this point I don't think. shame.

[g0bk1]

You could convert duration into seconds and use it in the visual and then use the measure noted above in the tooltip and that works ok.