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
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
gooranga1
Power Participant
Power Participant

You could create a measure below;

 

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

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

Anonymous
Not applicable

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

But thanks anyways @gooranga1

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

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. 

g0bk1_0-1666124488715.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.