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
Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old. Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with @konstantinos ages ago.
So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you.
Chelsie Eiden's Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = SUM([Duration]) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number RETURN // We put the hours, minutes and seconds into the proper "place" Hours * 10000 + Minutes * 100 + Seconds
All but the last line is the code from that article that @konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!!
eyJrIjoiYjE5ZDZkN2EtODdlNy00ZmUxLWIyOGItOWRhYjU0NDY2Y2VhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Thank you so much for the addition of days to the formula. This is exactly what I was looking for and it worked.
@VizKid Yep, you got it, good catch.
Awesome - Thanks so much @Greg_Deckler . You are amazing, really appreciate your help!! 🙂
@Patrick_AW Try changing the last line to:
Hours * 100 + Minutes
Then try a custom format string like 0000:00 or 00:00
Thanks Greg.
Custom format of 00:00 works with the above.
Thanks Greg.
It's working as expected and it resolved the issues in my report.
Thank you so much for your help.
Regards,
Umasankar
Hi all
Not going to suggest I 100% understand this, been working blind on Power Bi and came across this whilst lookingn to sum time to be more than 24:00:00.
This issue I have with this is that I want to use this to calculate the number of working hours based on a number of days, i.e 07:00:00 * 7 should be 49:00:00.
Due to the data, I have had to create a column called Working Hours, this figure is repeated against each action so I am having to use the average of that column against the number of days....with me so far? Not sure I am.
Anywho, when changing the Sum([Duration]) to Average([Working Hours])*{Days]
The rest remains the same however I get results such as 08:45:00 * 5 = 7.00 rather than 43:45:00
Anyone able to point me in the right direction here? Much appreciated.