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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
th3h0bb5
Resolver II
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

View solution in original post

3 REPLIES 3
AnneC
Advocate I
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.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors