Total of time in table

[Anonymous]

Hi All,

I have a table with data in time format as below, I would like to show the totals for this in the table. Can someone please let me know how to achieve?

Totals of the "Time" field is not showing up at the bottom

Thanks

Poojitha

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

For the time type, Power BI does not support sum aggregation. If you use dax to sum the time column, it will run into wrong value.

As a workaround, you can use the following dax to sum HH:MM SS format:

SumTime =
VAR TotalSeconds =
    SUMX (
        'Table Name',
        HOUR ( 'Table Name'[Column] ) * 3600
            + MINUTE ( 'Table Name'[Column] ) * 60
            + SECOND ( 'Table Name'[Column] )
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
VAR Secs =
    MOD ( TotalSeconds, 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors ) & ":"
        & IF ( Mins < 10, "0" & Mins, Mins ) & ":"
        & IF ( Secs < 10, "0" & Secs, Secs )

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

Best Regards,

Dedmon Dai

[collinq]

Hi @Anonymous ,

The system has to know what you are trying to do.  There is not a built-in function that I am aware of that will just take the time from one column to the next.  You will have to create a new column or use an existing column to determine the time frames that you are trying to achieve.  If you have a start time and end time you can subtract those and get the duration between them.  Or, you can subtract the second row time from the first row time if you are trying to get seconds between them.

All that said, you already have the seconds column in the first column.  Why not just divide that by 60 to get the number minutes and/or by another 60 to get the number of hours?

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!