Help - Converted decimal to hour BUT minutes and seconds are above 60

[Deeh]

Guys, I need your help.

I work with hours over 24 and I used in excel a column formatted in hour/37:50:55. In excel I created another column and converted it to decimal "cell*24 called worked hour DEC and I am doing my calculations in power BI on top of this column.

I created a formula in DAX and then formatted it as 00:00:00 which is working on the card and on the line and column graphs, however, the minutes and seconds are above 59. The hours are apparently correct and I need them to be accumulated over 24 hours but the minutes and seconds cannot exceed 59.

I also need to do the same calculation and the same types of graphs for downtime and the same thing is happening. With these same measurements I also need to calculate MTTR "MTTR = DIVIDE([Downtime],SUM(Base[Qty of failures]),0)" and MTBR "MTBF = DIVIDE([Worked hours]-[Downtime],[Failures],0)" and I'm also having problems with seconds and minutes over 24h. Can anyone help me identify where I'm going wrong? Please help me, I've tried every possible way I could find on the internet but it always goes wrong. This dax is the only one that works with all the options, the others give errors when converting text into numbers.

DAX 

Worked hours =

var vhour = INT(SUM(Base[Hours worked DEC]))

var vminute = INT((SUM(Base[Hours worked DEC]) - vhour) * 60)

VAR vsecond = ROUND(((sum  (Base[Hours worked DEC]) - vhour ) * 60 - vminute ) * 60,0)

var SS = IF(vsecond=0, vsecond & 0, vsecond)

var MM = FORMAT(vminute,"00")

var HH = FORMAT(vhour,"00")

RETURN

VALUE(HH&MM&SS)

 

[ryan_mayu]

@Deeh 

maybe you can change the last coding to 

RETURN

HH&":"&MM&":"&SS

[Deeh]

Hello, I'm a beginner in Power BI and I don't know how to convert hours to decimals or decimals to hours in this way. To convert hours to decimals in excel I created the column =cell*24 and in PBI I'm using this column to do the calculations, so they are already in decimals. but I don't know how to convert from decimals to seconds if that's what you meant. I did exactly what you wrote and it came up with a crazy big number.

[aj1973 · Accepted solution]

Ok Please follow these steps:

Open Power Query and Split column Hours Worked by delimiter "Colon" to get this

Rename columns

Add custom column

Change type of the custom column to numbers

Apply your formula or mine(as you like)

Here is mine if you want

Total Working =

var _seconds = SUM('Working hours'[Total in Seconds])

var _minutes = INT(_seconds / 60)

var _RemainingSeconds = MOD(_seconds, 60)

var _Hours = INT(_minutes / 60)

var _RemainingMinutes = MOD(_minutes, 60)

var _Days = INT(_Hours / 24)

var _RemainingHours = MOD(_Hours, 24)

return

_Days & " D : " &

_RemainingHours & " H : " &

_RemainingMinutes & " M : " &

_RemainingSeconds & " S  "

let me know

[aj1973]

Hi @Deeh 

Before starting using your formula you need to convert Hours Worked  to seconds then

Better use Power Query to convert it in seconds.