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

Calculate Average Duration in hh:mm:ss

Hello,

 

Like others, I'm attempting to run simple calculations which return duration values in HH:MMSmiley FrustratedS format.


I first went to this Aggregating Duration/Time blog post, but the end result is a string. I cannot display that on the Y axis or run an average or a MoM trend with this.

 

Then I tried this Formatting Time Values forum post, but it also produces a string.

 

Is there a way to:

 

- Take a numeric value that represents seconds (1800,3600,7200)

- Have it formatted to display as hh:mm:ss (0:00:30, 0:01:00, 0:02:00)

- Ran a calculation like an average on this previously formatted value (Avg of about 0:01:15)

1 ACCEPTED SOLUTION

Accepted Solutions
Steve_Wheeler Established Member
Established Member

Re: Calculate Average Duration in hh:mm:ss

Per this Duration as Y Axis forum post, the short answer is "not yet".  Per @Greg_Deckler, you might be able to sort the text duration result on your numeric duration column, but I doubt that will help in the scenario you describe.

 

I think it's a gap as well, and hope it's fixed soon - vote for this idea to get it improved: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

2 REPLIES 2
Steve_Wheeler Established Member
Established Member

Re: Calculate Average Duration in hh:mm:ss

Per this Duration as Y Axis forum post, the short answer is "not yet".  Per @Greg_Deckler, you might be able to sort the text duration result on your numeric duration column, but I doubt that will help in the scenario you describe.

 

I think it's a gap as well, and hope it's fixed soon - vote for this idea to get it improved: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

AnneC Frequent Visitor
Frequent Visitor

Re: Calculate Average Duration in hh:mm:ss

Hi 

We provide a service on a time based requirement, and as such, I have a solution for your enquiry, but it is only able to be displayed in a card, and not as a data set for an axis. However, it is interactive with any other graphics you use on the page and presents well in a report, so:

 

Working with a duration in seconds, to get a numeric average, you need to create a measure as follows using your seconds duration column in your table:

"Average Duration" = AVERAGE(File Name[Seconds Duration])

This will give you an average duration, but the seconds will be a figure format and not a time format.

 

You then need to do a second measure, which is based on the "Average Duration" measure that is created as above, by using the following formula:

"Time Format Average Duration" = (FORMAT([Average Duration]/60/60/24,"HH:MMSmiley FrustratedS")

This will display the figure in the time format HH:MMSmiley FrustratedS. This can then be displayed in a card.  

 

As stated above, this figure is still interactive with any charts you may create.

 

Note though: this works well for positive averages, however, whilst it does calculate negative averages correctly, it does not show the minus "-" sign.  I worked around this by putting in a second card with the following formula:

"Average Performance Positive Negative" = IF(Time Format Average Duration]<0,"AVERAGE UNDER BY:","AVERAGE OVER BY:"

I then presented the two boxes together, and because of the formulas, they change either independently, or together, depending on the positives and negatives outcome of the average.

 

I hope this helps.