cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Sum up the time format

Hi,

I have a data like mentioned below. The Time Spent (HH : MM SS) column is a string but i want it as a time to sum it up.

 Name Time Spent (HH : MM : SS) Day Abhi 00:50:00 Day 1 Ankith 00:15:00 Day 1 Joseph 01:10:00 Day 1 John 00:30:00 Day 1 Abhi 00:10:00 Day 2 Ankith 00:35:00 Day 2 Joseph 01:20:00 Day 2 John 02:30:00 Day 2 Abhi 00:20:00 Day 3 Ankith 01:20:00 Day 3 Joseph 00:13:00 Day 3 John 00:15:00 Day 3

If I group it by Name or Day,  I need the sum of the total time they spent as mentioned below.

 Name Time Spent (HH : MM : SS) Abhi 01:20:00 Ankith 02:10:00 Joseph 02:43:00 John 03:15:00

 Day Time Spent (HH : MM : SS) Day 1 02:45:00 Day 2 04:35:00 Day 3 02:08:00

I have a working solution for the same

1. copied the Time Spent (HH : MM : SS) column to a new column, say named TimeCopy
2. changed the TimeCopy to date/time type
3. created a new measure (New Duration) as below

```NewDuration =
VAR TotalSeconds=SUMX('Table',HOUR('Sheet2 (2)'[TimeCopy])*3600+MINUTE('Table'[TimeCopy])*60+SECOND('Table'[TimeCopy]))
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)```

The above measure is working for me as per my requirement, but I'm not able to use it in Value field.

Is there a way where I can use this measure in Value field? Or Can someone suggest any workaround for the same, please?

Thanks,

Akhil

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

Re: Sum up the time format

@Anonymous,

I was not able to reproduce your issue where you were unable to use your measure in the Value Field.

Please review what I did do to see if this is a possible solution for you.

Edit - added new picture: The above screen capture shows the final Custom Column Added in the Query Editor, below is the Query Settings for the Add Column Custom Column 'Time Spent' that produces the above image.

Edit - Close & Apply returns you to Power BI where I added a different Time Format to 'Time Spent' to more align with what you had in your original post. See below.

Now you can do a SUM( ) measure of 'Time Spent' which I called Duration, also show below.

The Measure First New Duration was what I achieved with your code. However, I could not get what I believed you desired.

Spoiler
```New Duration =
VAR TotalSeconds=SUMX(Table2,HOUR(Table2[Time Spent])*3600+MINUTE(Table2[Time Spent])*60+SECOND(Table2[Time Spent]))
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)```

Result Visuals:

Oh, I wasn't able to create a leading zero on the hour, but I was testing what adding 12 hours to John on Day 1 and what the display would look like.

Proud to be a Super User!

7 REPLIES 7
Super User I

Re: Sum up the time format

@Anonymous,

I was not able to reproduce your issue where you were unable to use your measure in the Value Field.

Please review what I did do to see if this is a possible solution for you.

Edit - added new picture: The above screen capture shows the final Custom Column Added in the Query Editor, below is the Query Settings for the Add Column Custom Column 'Time Spent' that produces the above image.

Edit - Close & Apply returns you to Power BI where I added a different Time Format to 'Time Spent' to more align with what you had in your original post. See below.

Now you can do a SUM( ) measure of 'Time Spent' which I called Duration, also show below.

The Measure First New Duration was what I achieved with your code. However, I could not get what I believed you desired.

Spoiler
```New Duration =
VAR TotalSeconds=SUMX(Table2,HOUR(Table2[Time Spent])*3600+MINUTE(Table2[Time Spent])*60+SECOND(Table2[Time Spent]))
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)```

Result Visuals:

Oh, I wasn't able to create a leading zero on the hour, but I was testing what adding 12 hours to John on Day 1 and what the display would look like.

Proud to be a Super User!

Anonymous
Not applicable

Thanks,

Akhil

Super User I

Re: Sum up the time format

hi @Anonymous,

I've edited my post, showing the step of using the Query Editor to change your text string 'Time Spent(HH:MM:SS)' into actual time value so you can do math. Interestingly enough, using the Query Editor, it automatically added a step to change the Type of text string 'Time Spent(HH:MM:SS)' to actual time for me.  I took out that step to attempt to match your dataset.

Hope it works for you now.

Proud to be a Super User!

Frequent Visitor

Re: Sum up the time format

try step by step everything that they indicate but I just went around in circles I need to do something as simple as adding the hours

Super User I

Re: Sum up the time format

It looks like you're having Data Type problems.

`= [Hora_Final] - [Hora_Inicio]`

Change the Data Type to 'Duration'

Close & Apply.

Change the Data type in Power BI to Time and Format as 13:30:55 (H:mm:ss); this would be if you needed to format the calculated column

Create a measure (Format as above for the measure):

`Measure = SUM(Reposiciones[Custom])`

Results as:

Proud to be a Super User!

Visitor

Re: Sum up the time format

Hello!!

I've been trying to use your solution, and followed all the steps exactly the same. And it works perfectly with a short amount of data:

But whenever i try using a more large amount of data it doesnt summarize correctly. do you have any idea on why it behaves like this?

Super User I

Re: Sum up the time format

My guess is because the format is 'hh:mm:ss' and a sum greater than 24 ('hh') cannot be displayed correctly. I believe most people would just convert to decimal hours. I have not seen a good (they work; it is just a pretty involved formula) solution for formatting  as 00:00:00:00 where it represents dd:hh:mm:ss.

Proud to be a Super User!

Announcements

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors