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 :)
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
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 ?
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
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 ?
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
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 :)
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
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#")
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#")
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
