How to get an Average Number based on hh:nn:ss format?

[svelazquez]

Hi everyone,

I am trying to get an Average of one Column with the following formtat: (hh:nn:ss) 

When using the SUM calculation, it gives me the right number , however, when I change the calculation to AVERAGE, it changes the result format automatically.

My question here is, what can I do to get the AVERAGE of my column with the same format (hh:nn:ss)?

[v-yangliu-msft]

Hi  @svelazquez ,

Here are the steps you can follow：

1. Enter Power Query, select Date column - Add Column - Time - select Hour / Minute / Second to create three columns respectively..

2. Create calculated column.

seconds =
[Hour]*60*60 + [Minute]*60 + [Second]

Duration Average =
RIGHT (
    "0"
        & INT (
            (
                AVERAGEA ( 'Table'[seconds] )
                    - INT ( AVERAGEA ( 'Table'[seconds] ) / ( 24 * 60 * 60 ) ) * ( 24 * 60 * 60 )
            ) / 3600
        ),
    2
) & ":"
    & RIGHT (
        "0"
            & INT (
                (
                    AVERAGEA ( 'Table'[seconds] )
                        - INT ( AVERAGEA ( 'Table'[seconds] ) / 3600 ) * 3600
                ) / 60
            ),
        2
    ) & ":"
    & RIGHT (
        "0"
            & (
                AVERAGEA ( 'Table'[seconds] )
                    - INT ( AVERAGEA ( 'Table'[seconds] ) / 60 ) * 60
            ),
        2
    )

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[svelazquez]

Hi, thanks for your help. Just one more question, at the time I try to insert the second formula (Duration Avg) it says the expression "RIGHT" is not recognized by the system. 

[amitchandak]

@svelazquez , First convert data to an hour to an Average and convert back to a time

Measure =

var _hr = AverageX(Table,  hour([Time])+ minute([Time])/60 + second([Time]) )

return

Time(0,0,0) +_hr

[svelazquez]

please, can you tell me more about how to do this process? I didn't completely understand, sorry

[svelazquez]

But I first need to create a table right?