cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
akhilduvvuru10 Regular Visitor
Regular Visitor

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
ChrisMendoza Senior Member
Senior Member

Re: Sum up the time format

@akhilduvvuru10,

 

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.

View solution in original post

5 REPLIES 5
ChrisMendoza Senior Member
Senior Member

Re: Sum up the time format

@akhilduvvuru10,

 

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.

View solution in original post

akhilduvvuru10 Regular Visitor
Regular Visitor

Re: Sum up the time format

Hi @ChrisMendoza,

 

Thanks for your help!

 

 

Thanks,

Akhil 

ChrisMendoza Senior Member
Senior Member

Re: Sum up the time format

hi @akhilduvvuru10,

 

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.

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

 

ChrisMendoza Senior Member
Senior Member

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

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 295 members 3,412 guests
Please welcome our newest community members: