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.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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