Average duration

[Anonymous]

Hello,

I want to get an average duration and change the format, any ideas ? i try many things put ended up with an issue...

I also would like to change the format so it is HH:mm:ss,

Regards,

[ppm1]

Please see this article - Calculate and Format Durations in DAX – Hoosier BI

Pat

[ronrsnfld]

Neither Power Query nor Power BI have a duration format where Hours can be 24 or higher.

So you would need to create a Text String to show that kind of result.

The text string would not be usable as is in future calculations.

Here is some sample code:

let
    Source = Table.FromColumns(
    {
        {#duration(0,0,26,4),
        #duration(0,0,25,26),
        #duration(0,0,33,42),
        #duration(0,0,34,2),
        #duration(0,2,17,10),
        #duration(0,2,16,47),
        #duration(0,17,29,50)}
    }, type table[Durations=duration]),
    

    avg = Duration.TotalHours(List.Average(Source[Durations])),
    hrs = Number.IntegerDivide(avg,1),
    tMin = Number.Mod(avg,hrs)*60,
    min = Number.IntegerDivide(tMin,1),
    sec = Number.Round(Number.Mod(tMin,min)*60,0),

    fmt = Number.ToText(hrs * 10000 + min * 100 + sec,"00:00:00")
    
in
    fmt

Result

03:26:09

[AlienSx]

Hello, @Anonymous try Time.From(Duration.TotalHours(List.Average(your_list)) / 24). But if Duration.TotalHours goes over 1 then you end up with error. 

[Anonymous]

Hello,

Well i did ended up with an error, but thanks for trying