cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. 

 

NameTime Spent (HH : MM : SS)Day
Abhi00:50:00Day 1
Ankith00:15:00Day 1
Joseph01:10:00Day 1
John00:30:00Day 1
Abhi00:10:00Day 2
Ankith00:35:00Day 2
Joseph01:20:00Day 2
John02:30:00Day 2
Abhi00:20:00Day 3
Ankith01:20:00Day 3
Joseph00:13:00Day 3
John00:15:00Day 3

 

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

NameTime Spent (HH : MM : SS)
Abhi01:20:00
Ankith02:10:00
Joseph02:43:00
John03:15:00

 

DayTime Spent (HH : MM : SS)
Day 102:45:00
Day 204:35:00
Day 302: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.

 

 

Value.JPG

 

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

 

1.PNG

 

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. 

 

4.PNG

 

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.

 

2.PNG

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:

3.PNG

 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Super User I
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.

 

1.PNG

 

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. 

 

4.PNG

 

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.

 

2.PNG

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:

3.PNG

 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

Re: Sum up the time format

Hi @ChrisMendoza,

 

Thanks for your help!

 

 

Thanks,

Akhil 

Super User I
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




RodolfoGuti Frequent Visitor
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

 

2018-06-18_17-27-35.png2018-06-18_17-26-50.png2018-06-18_16-55-25.png

 

Super User I
Super User I

Re: Sum up the time format

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

 

1.PNG

 

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

 

2.PNG

 

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

 

Measure = SUM(Reposiciones[Custom])

Results as:

3.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Deborahrue Visitor
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:

 

1.PNG

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?

Capture.PNG

Super User I
Super User I

Re: Sum up the time format

@Deborahrue -

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors