Seconds to Hours Minutes sum

[Anonymous]

Guys, I appreciate there have been lots of threads on this but i still cant seem to find a solution.  I have huge data table showing how long a certain agent has been on the phone in seconds.  We need to see in hours how long each agent is on the phone for over a weekly period.  

There are loads of people trying to solve this here but i have not come acrooss a solutuion that shows hours per day with a total .  For example, in excel this can easily be done by adding a new column and divide the seconds column by 86400. Then change this to time format.  This allows you to see all the data in a table with totals at the bottom.  For example, i can see over the week this agent has been on calls for 34 hours 43 minutes. Can anyone solve this which allows me to see the eact same thing in a Power BI table?

[Icey]

Hi @Anonymous ,

Try this:

Measure = 
SUMX ( 'table', HOUR ( 'Table'[HH:SS] ) )
    + TRUNC ( SUMX ( 'table', MINUTE ( 'Table'[HH:SS] ) ) / 60 ) & ":"
    & MOD ( SUMX ( 'table', MINUTE ( 'Table'[HH:SS] ) ), 60 )
    & (
        IF (
            LEN ( MOD ( SUMX ( 'Table', MINUTE ( 'Table'[HH:SS] ) ), 60 ) ) > 1,
            ":00",
            "0:00"
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks but this doesnt seem to be calcualting correctly. Power BI is slightly out compared to excel.  So the result should be 03:50:44 but power BI is showing 03:39:00.  See examples below:

[Icey]

Hi @Anonymous ,

Sorry, the previous measure is a bit problematic. Try this:

Sum Measure 2 =
SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) )
    + TRUNC ( SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ) / 60 ) & ":"
    & FORMAT (
        MOD ( SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ), 60 )
            + TRUNC ( SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ) / 60 ),
        "00" & ":"
            & FORMAT (
                MOD ( SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ), 60 ),
                "00"
            )
    )

The total result in Excel is also 3:50:34.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

No need to be sorry i appreciate all your help with this.  Its works almost perfrectly except i noticed the following difference.  

Power BI shows 163 minutes rather than 43

[Icey · Accepted solution]

Hi @Anonymous ,

How about this?

Sum Measure 2 =
VAR Seconds1 =
    SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) )
VAR Minutes1 =
    SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) )
VAR Hours1 =
    SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) )
VAR Seconds2 =
    MOD ( Seconds1, 60 )
VAR Minutes2 =
    MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
    Hours1
        + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
    Hours2 & ":"
        & FORMAT ( Minutes2, "00" ) & ":"
        & FORMAT ( Seconds2, "00" )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That is perfect thank you so much for your help

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/Decimal-to-time-in-a-mesure/m-p/887981#M425662

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[parry2k]

@Anonymous did you looked at similar post here.

[Anonymous]

Just read it and thanks for this.  I have added the following but cant seemt to get the addtional code below to work properly.  Can you help add the ------if(len(mod etc--------------- to my code below:

Measure1 = sumx('Calls SQL', hour('Calls SQL'[Out Time])) + TRUNC(sumx('Calls SQL', MINUTE('Calls SQL'[Out Time]))/60) & ":" & mod(sumx('Calls SQL', MINUTE('Calls SQL'[Out Time])), 60) & ":00"

Additional code

IF(len(mod(sumx(Base_Pautas; MINUTE('table', MINUTE('table'[time])); 60))>1;":00";"0:00")