Problems with hour format greater than 24 hours

[Shaik_amir]

Hi there,

How to find average of time which is more then 24 hours in power Bi like 158 hours is average time taken by lab to complete one report of patient.

example this is below data from excel. Average formula is used.

Please help

[Anonymous]

DAX Mode ( In this mode you can agregate for any dimension)

QtSegundos = SUM([Seconds])

Time=
VAR Horas = INT([QtSegundos]/3600)
VAR Minutos = INT(([QtSegundos] - (Horas * 3600))/60)
VAR Segundos = MOD([QtSegundos];60)
RETURN
Horas&":"&FORMAT(Minutos;"00")&":"&FORMAT(Segundos;"00")

[fhill]

....  This is as close as I can get to what you are tryingn to do....

I have broke up your TEXT data by delimiter then recalculated a custom column to determine your duration in **DAYS**.  Days is required to conver to Duration (Not pictured);  I 'Change Type' in Power BI Query Editor to convert the newly calcualted column back into a Duration.  This changes your 53:22:40 example into 2.05:22:40 in Duration Formatting.

Now that I have a Duration formatted column, I can easily Average in Power BI.  The hard part (where i'm stuck) is converting the duration Average back into HH:MM:SS foramt??  My 2nd screen shot below is Average Hours, mabye someone can pick up it from here???

FOrrest

[Shaik_amir]

Thanks for your help pal.

Really appreciate this but inspite of splitting the data cant we do the same with exicting format of time as 53:22:40  HH:MM:SS.

Thanks alot

[Anonymous]

Hi @Shaik_amir,

So you want to convert the result to time format, right?
If this is a case, you can take a look at below link, and use 'TIMEVALUE' function to convert these text to time value:

Converting Decimal Hours to Time Format

TIMEVALUE Function (DAX)

Regards,

Xiaoxin Sheng