Forum Discussion
Format the average of a duration
- 9 years ago
This is something that I talked extensively with the Power BI product group about a couple weeks ago at the MVP Summit in Seattle. After a lot of discussion about the issue, I believe we finally settled on the correct owner and established a way to move forward. You can vote for the Idea here:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type
The big use case blocker is for call centers or anything like a call center where durations are critically important.
This is something that I talked extensively with the Power BI product group about a couple weeks ago at the MVP Summit in Seattle. After a lot of discussion about the issue, I believe we finally settled on the correct owner and established a way to move forward. You can vote for the Idea here:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type
The big use case blocker is for call centers or anything like a call center where durations are critically important.
- Anonymous9 years agoNot applicable
Here's a variant of the DAX from Greg_Deckler et al as discussed in http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/bc-p/91032#M328 It uses Duration rather than number of seconds to calculate an Average.
Duration Average = // Duration formatting // * @konstatinos 1/25/2016 // * Steve Wheeler 22/11/2016 - converts from days (default format for Power Query's) Duration data type, rather than seconds // * Given a number of days, returns a format of "d.hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = AVERAGE(Calls[Call Duration]) // The days will be the whole part of the Duration VAR Days = INT (Duration) // There are 24 hours in a day VAR Hours = INT ( (Duration - Days) * 24) // There are 60 minutes in an hour VAR Minutes = INT ( (Duration - Days - (Hours / 24) ) * 24 * 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP((Duration - Days - (Hours / 24) - (Minutes / (24 * 60) ) ) * 24 * 60 * 60, 0) // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( Days, CONCATENATE (".", CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) ) )Change the column on which you calculate in the VAR towards the top and it should work in your table but, as text rather than numeric, can't be used as a chart axis or in other measures etc.
Bring on that Duration data type...:)