cancel
Showing results for
Did you mean:
DamodarReddy Frequent Visitor

I have to sum the time in a particular format.

for Example :

9:26:22

9:22:14

9:12:27

9:05:25

9:15:24

Sum is 46:21:52 - i have to get in this format.

But, when i am trying to do in power i am getting the sum as 22:21:52.

i am trying to change the format in power bi, i am not getting the sum(46:21:52).

In Excel we are changing the format to get sum like(46:21:52) which is  [h]:mm:ss:@

These types of formats are not available in Power bi.

is there any alternate solution for this.

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

```New Time =
VAR a =
SUMX (
Table1,
HOUR ( Table1[Time] ) * 3600
+ MINUTE ( Table1[Time] ) * 60
+ SECOND ( Table1[Time] )
)
VAR hours =
TRUNC ( a / 3600 )
VAR minutes =
TRUNC ( MOD ( a, 3600 ) / 60 )
VAR seconds =
MOD ( a, 60 )
RETURN
hours & ":"
& minutes
& ":"
& seconds
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6 Super User

I have done a bunch of different stuff regarding time/duration type of stuff. Check out these links:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279

Proud to be a Datanaut!

Highlighted Community Support Team

You may create below measure.

```NewTime =
VAR a =
SUMX (
'Time',
HOUR ( 'Time'[Time] ) * 3600
+ MINUTE ( 'Time'[Time] ) * 60
+ SECOND ( 'Time'[Time] )
)
VAR hours =
TRUNC ( a / 3600 )
VAR minutes =
TRUNC ( MOD ( a, 3600 ) / 60 )
VAR seconds =
MOD ( a, 60 )
RETURN
hours & ":"
& minutes
& ":"
& seconds
``` Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DamodarReddy Frequent Visitor

New Time = var a = SUMX(Hour(Table1[Time])*3600 + MINUTE(Table1[Time])*60 +SECOND(Table1[Time]) var hours = TRUNC(a/3600) var minutes = TRUNC(MOD(a,3600)/60) var seconds = MOD(a,60) return hours & ":" & minutes & ":" & seconds

i am using two columns one is date & time

Date                    Time

1/7/2017               9:16:25

2/7/2017               9:21:30

3/7/2017               9:22:45

when i an using as columns i am not able to get the name of the column SumX(Hour(i am not getting here that column(Time)).

if i create a measure like Sum(table1(time)). then i can use that measure over here  SUMX(Hour(Table1[Time])*3600. Even tough i am using this i am getting error i.e, your are using to many hour arguments. Community Support Team

```New Time =
VAR a =
SUMX (
Table1,
HOUR ( Table1[Time] ) * 3600
+ MINUTE ( Table1[Time] ) * 60
+ SECOND ( Table1[Time] )
)
VAR hours =
TRUNC ( a / 3600 )
VAR minutes =
TRUNC ( MOD ( a, 3600 ) / 60 )
VAR seconds =
MOD ( a, 60 )
RETURN
hours & ":"
& minutes
& ":"
& seconds
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DamodarReddy Frequent Visitor

Hi,

Example :   In Excel - 4:46:35 we can change that format to 64:46:35(Custom format - [h]:mm:ss;@)

But in power BI  how can we change that this time (4:46:35) to 64:46:35. Community Support Team