Sum up the time format

[Anonymous]

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

[RodolfoGuti]

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

[ChrisMendoza]

@RodolfoGuti,

It looks like you're having Data Type problems.

Add a custom column as:

= [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:

[Anonymous]

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?

[Anonymous]

Hi - Did you ever find a solution to this? I'm having the same issue. 

Smaller subsets below a certain amount of hours total up, but not larger sets. 

[ChrisMendoza]

@Anonymous -

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.

[ChrisMendoza · Accepted solution]

@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.

[Anonymous]

Hi @ChrisMendoza,

Thanks for your help!

Thanks,

Akhil 

[ChrisMendoza]

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.