Forum Discussion
Duration Time more than 24 hours
Hi,
once we group one cell and having multiable duration and SUM of them is equal more than 24 hours as duration is not appeared all total example 20:10:10 (HH:MM:SS) + 10:10:05 it should shows as 30:20:15 ..
Any help here ?
Hi amerjaroudi
You need to treat travel time as a duration in Power Query then bring that into PBI Desktop where it will be a decimal.
You can then write a measure to display your travel time in a friendly H:M:S format - see this solution for code and an explanation
Solved: Converting decimal hours to friendly format (D:H:M... - Microsoft Power BI Community
Leaving the travel time as decimal in PBI means you can do maths with it.
You can uplaod your PBIX file to OneDrive or Dropbox and then link to it from here.
Regards
Phil
4 Replies
- PhilipTreacy
Super User
Hi amerjaroudi
You need to treat travel time as a duration in Power Query then bring that into PBI Desktop where it will be a decimal.
You can then write a measure to display your travel time in a friendly H:M:S format - see this solution for code and an explanation
Solved: Converting decimal hours to friendly format (D:H:M... - Microsoft Power BI Community
Leaving the travel time as decimal in PBI means you can do maths with it.
You can uplaod your PBIX file to OneDrive or Dropbox and then link to it from here.
Regards
Phil
- mahoneypat
Microsoft Employee
If your column is already a duration, you can add a custom column with
= Duration.TotalHours([DurationColumn]
If your column is a Time, you can add a custom column with
= Duration.TotalHours([Time]-#time(0,0,0))
Either way, you'll end up with hours as a decimal that you can then add up for analysis. You could use the other Duration.Totalxxx functions if you don't want hours.
Pat
- PhilipTreacy
Hi amerjaroudi
Please always supply sample data so we don't have to guess what you are doing.
I presume you are talking about Durations in Power Query? But by the format you are using you may be using Times rather than actual Durations?
A Duration in Power Query is actually a decimal number describing whole days e.g. 1.25 is 1 day and 8 hours.
When you bring these durations into Power BI (the Data Model) you'll see them as decimals.
So to add durations you first must store them as durations, and adding them is just as simple as adding decimals.
Please provide some sample data so I can see what you are actually doing.
Regards
Phil
Thanks for your reply ..
I made time differance between two columns "Depart" and "Arrival" and called "Travel_Time" as duration in (Query Editor) then I choose "Region" coulmn to table visualization and didn`t find (sum of travel_time) only count found it,, I make new measure of Travel time "
SumTravel = SUM('CM History'[Travel_Time])" and added to same table, first row (region_1) the sum should be 1923:47:21, this is my concene here it not showing if more than 24 hoursalso let me know how I can upload the sample data source
