duration format

[Anonymous]

Hello, I have a duration column in Power query, but it transforms in decimals when I go to the data panel. When I do an average, it is all in decimals. I want to be able to show the average in a time format: 00:00:43 (43 secondes). I searched everywhere on the web and there is no solution. Thank you

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can follow these steps to calcualte:

1. Change the data type of duration column to HH:MM:SS:

2. Create a calculate column to get the seconds:

Second = SECOND('Table'[Duration])

3. Create a measure to calculate the average seconds:

Average =
VAR _avg =
    AVERAGE ( 'Table'[Second] )
VAR HoursPart =
    MOD ( INT ( _avg / 3600 ), 24 )
VAR MinPart =
    MOD ( INT ( _avg / 60 ), 60 )
VAR SecPart =
    MOD ( _avg, 60 )
RETURN
    FORMAT (
        CONVERT ( HoursPart & ":" & MinPart & ":" & SecPart, DATETIME ),
        "hh:mm:ss"
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MoE2307]

What if i need to calculate the Duration in Minutes, Hours or all together?

[amitchandak]

@Anonymous , You need to take avg in decimal and convert it back to duration for display

example

quotient(Seconds1,3600 ) & ":" & FORMAT ( quotient(mod(Seconds1,3600 ),60), "00" ) & ":" & FORMAT ( mod(mod(Seconds1,3600 ),60), "00" )

refer if these solution can help

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx