cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IsadoraAlmeida6 Regular Visitor
Regular Visitor

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
v-yulgu-msft Super Contributor
Super Contributor

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

Hi @IsadoraAlmeida6,

 

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

8 REPLIES 8
gooranga1 Senior Member
Senior Member

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")
IsadoraAlmeida6 Regular Visitor
Regular Visitor

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

gooranga1 Senior Member
Senior Member

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.

v-yulgu-msft Super Contributor
Super Contributor

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

Hi @IsadoraAlmeida6,

 

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

IsadoraAlmeida6 Regular Visitor
Regular Visitor

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
Super User

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

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
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.

SANTOSHVAIDYA New Member
New Member

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

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 372 members 3,129 guests
Please welcome our newest community members: