98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » How to show time over 24 hours
How to show time over 24 hours
Resolved · High Priority · Version 365
Wed 8 Nov 2023, 15:47
Edited on Wed 8 Nov 2023, 15:48Delegate Steven said...
Steven has attended:
Power BI Modelling, Visualisation and Publishing course
How to show time over 24 hours
I currently have a data output which gives me time in whole number minutes (E.g 450 which is is 07:30 when converted to hours). As i am wanting to covert this to a time format, i divide this by 1440 which gives me the decimal number. When i try and put this into duration it gives me the correct time format (Fab) but when i sum it in a table or graph it doesn't go above 24 hours.
Please could someone help me get this so it shows over 24 hours ?
so for example if i have 10 people do a specific work segment for 3 hours each i would want it to show as: 30:00
Thank you :)
Thu 9 Nov 2023, 12:02Trainer Jens said...
RE: How to show time over 24 hours
Hi Steven,
Thank you for the forum question.
Try the DAX below. 'table' is the table name of your table with the time, and 'table'[Time] is your table name and your heading inside the []
total_time =
VAR totalMinutes = SUMX('table', HOUR('table'[Time])*60 + MINUTE('table'[Time]))
var hours = TRUNC(totalsecs/60)
var minutes = TRUNC(MOD(totalsecs, 3600))
RETURN hours & ":" & minutes
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 9 Nov 2023, 14:26
Edited on Thu 9 Nov 2023, 14:27Delegate Steven said...
RE: How to show time over 24 hours
Thank you for coming back too me,
Do i need to use that in a new measure or do i need to put that in a new custom column in the transform data part ?
i tried using it in a new measure but it says that it doesn't know what Totalsecs is ? do i need to define that somewhere ?
Thu 9 Nov 2023, 14:35Trainer Jens said...
RE: How to show time over 24 hours
Sorry Steven,
It is a new measure.
Please try this and me know if my logic is correct this time.
total_time =
VAR totalMinutes = SUMX('table', HOUR('table'[Time])*60 + MINUTE('table'[Time]))
var hours = TRUNC(totalMinutes/60)
var minutes = TRUNC(MOD(totalMinutes, 60))
RETURN hours & ":" & minutes
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 9 Nov 2023, 14:38Delegate Steven said...
RE: How to show time over 24 hours
Thank i'll try that.
Do i need to refer it to my data output column which has the whole number (e.g 450 for 7:30 hours) or to the new custom column which has a decimal from a formula i have created ?
Thu 9 Nov 2023, 14:45Trainer Jens said...
RE: How to show time over 24 hours
Sorry again Steven,
Normally people already have it in hours and minutes but you have it in minutes
Try this, you may have to work with formatting
total_time =
VAR totalMinutes = SUM('table'[Time])
var hours = TRUNC(totalMinutes/60)
var minutes = TRUNC(MOD(totalMinutes, 60))
RETURN hours & ":" & minutes
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 9 Nov 2023, 14:49Delegate Steven said...
RE: How to show time over 24 hours
That seems to work now thank you :)
Although when minutes is a single value (E.g 1 Minute) it shows as 89:1. Is there any way to show minutes with a leading zero ?
i have the same if it is 240 hours and 0 mins it shows as 240:0
i would like it if it shows as 240:00 if possible :)
Thu 9 Nov 2023, 15:05Trainer Jens said...
RE: How to show time over 24 hours
Try
total_time =
VAR totalMinutes = SUM('table'[Time])
var hours = TRUNC(totalMinutes/60)
var minutes = TRUNC(MOD(totalMinutes, 60))
RETURN if(len(minutes)=1,hours & ":" & FORmat(minutes,"0#"),hours & ":" & minutes)
FORMAT(<a numeric value>, "0#")
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 9 Nov 2023, 15:18Delegate Steven said...
RE: How to show time over 24 hours
What numerical value should i replace with in this section of the formula ?
FORMAT(<a numeric value>, "0#")
Thu 9 Nov 2023, 15:19Delegate Steven said...
RE: How to show time over 24 hours
Actually - if i take out that part it seems to work without the below:
FORMAT(<a numeric value>, "0#")
Thu 9 Nov 2023, 15:24Trainer Jens said...
RE: How to show time over 24 hours
Yes it is me again. I just pasted the structure but forgot to delete it again.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Create Interactive DashboardsBuild interactive dashboards by leveraging features like slicers, drill-through, and bookmarks. Slicers allow users to filter data dynamically, while drill-through enables detailed exploration of specific data points. Bookmarks help you save the current view, making it easy to switch between different states of your report. |
