Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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)
Solved! Go to 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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
74 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |