Calculate Average Duration in hh:mm:ss

[th3h0bb5]

Hello,

Like others, I'm attempting to run simple calculations which return duration values in HH:MM:SS 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)

[AnneC]

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:MM:SS")

This will display the figure in the time format HH:MM:SS. 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.

[Amaury_VanEspen]

Hello AnneC,

it seems the feature has not yet been implemented, the status is still under review.

Have you got new insight to set the average duration using an axis ?

Thank you

Amaury

[Anonymous · Accepted solution]

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