Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver II

## Calculate Average Duration in hh:mm:ss

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)

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

3 REPLIES 3
Advocate I

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.

Frequent Visitor

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

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

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors