Power BI - Conversion of seconds to hours in DAX

Question

I have a table containing User with Seconds Spent.

I want to convert seconds into hours. And Show total hours in hh:mm format.

But the sum of seconds is 400020 which is equal to 111.116667 hour in this case my below formula is not working as expected.

RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

Taken this formula from Power BI Community

When I am using this measure then the actual hours should be 113:33 but it is showing 13:33. — Sonali

Foxan Ng Foxan Ng · Accepted Answer · 2017-08-03T10:05:21

The RIGHT function is cutting your hour digits if there are more than 2 digits.

Based on your use case you can change it to

RIGHT ( "00" & INT ( TableName[Duration] / 3600 ), 3 )

if you have 3 digits for hour.

or you can just remove the RIGHT function for the hour part if you don't need the leading zero padding at all:

INT ( TableName[Duration] / 3600 )

Power BI - Conversion of seconds to hours in DAX

2 Answers