cancel
Showing results for
Did you mean:
Helper II

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

Accepted Solutions
Microsoft

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

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.
8 REPLIES 8
Highlighted
Impactful Individual

## Re: How to calculate the median of a time column (HH:MM:SS)?

You could create a measure below;

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

## Re: How to calculate the median of a time column (HH:MM:SS)?

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

But thanks anyways @gooranga1

Impactful Individual

## Re: How to calculate the median of a time column (HH:MM:SS)?

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

Microsoft

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

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.
Helper II

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

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

Super User III

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

Hey,

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

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Anonymous
Not applicable

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

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.

New Member

## Re: How to calculate the median of a time column (HH:MM:SS)?

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors