Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

8 REPLIES 8
RodolfoGuti
Regular Visitor

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

 

@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!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

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

Anonymous
Not applicable

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. 

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

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



ChrisMendoza
Resident Rockstar
Resident Rockstar

@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!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

Hi @ChrisMendoza,

 

Thanks for your help!

 

 

Thanks,

Akhil 

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!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.