Calculate average time per day

[Trosa_220568]

Hi, how can I calculate the average process time in hours and minutes per day from the data below?

Date	Deal	Process Time
19/07/2021	Deal 1	00:38
19/07/2021	Deal 2	00:42
19/07/2021	Deal 3	00:23
20/07/2021	Deal 4	00:58
20/07/2021	Deal 5	00:17
21/07/2021	Deal 6	01:05
21/07/2021	Deal 7	01:46
22/07/2021	Deal 8	00:27
22/07/2021	Deal 9	00:26
22/07/2021	Deal 10	02:49
22/07/2021	Deal 11	00:33
23/07/2021	Deal 12	01:15
23/07/2021	Deal 13	00:22
23/07/2021	Deal 14	00:39
23/07/2021	Deal 15	00:28

Thanks

[Fowmy · Accepted solution]

@Trosa_220568 

You can create a measure as follows:

Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")

[FrankAT · Accepted solution]

Hi @Trosa_220568 ,

with DAX you can do it like this. It looks a little bit weird but the time formating with DAX is buggy. 🤔

Average Process Time =
VAR _AverageTimeInDecimal =
    CALCULATE (
        AVERAGE ( 'Table'[Process Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Date] )
    )
VAR _Hours =
    INT ( _AverageTimeInDecimal * 24 )
VAR _Minutes =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 ) * 24 * 60 )
VAR _Seconds =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 - _Minutes / 1440 ) * 24 * 3600 )
RETURN
    FORMAT ( _Hours, "00\:" ) & FORMAT ( _Minutes, "00\:" )
        & FORMAT ( _Seconds, "00" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[v-easonf-msft · Accepted solution]

Hi,  @Trosa_220568 

If you want to calculate the average processing time in hours and minutes per day, you need to make some small adjustments to @CNENFRNL ‘s method.

When you specify the column to group by and the desired output, you need to select the Operation "Average" rather than  Operation "Sum".

Result:

Best Regards,
Community Support Team _ Eason

[FrankAT · Accepted solution]

Hi @Trosa_220568 ,

with DAX you can do it like this. It looks a little bit weird but the time formating with DAX is buggy. 🤔

Average Process Time =
VAR _AverageTimeInDecimal =
    CALCULATE (
        AVERAGE ( 'Table'[Process Time] ),
        ALLEXCEPT ( 'Table', 'Table'[Date] )
    )
VAR _Hours =
    INT ( _AverageTimeInDecimal * 24 )
VAR _Minutes =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 ) * 24 * 60 )
VAR _Seconds =
    INT ( ( _AverageTimeInDecimal - _Hours / 24 - _Minutes / 1440 ) * 24 * 3600 )
RETURN
    FORMAT ( _Hours, "00\:" ) & FORMAT ( _Minutes, "00\:" )
        & FORMAT ( _Seconds, "00" )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Fowmy · Accepted solution]

@Trosa_220568 

You can create a measure as follows:

Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")

[BJGRUNDY]

How do I get this to work and apply any filters I have?

[Barryjvr]

I am new to this blog and struggle with the following: Calculate the average resolution time of the following Columb

Your help will be apreciated!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA7DoQwDEXRrYxSI2E/J+RTzy4QxRR07L+GYNOEcRfpvEjWXdfAdaY8g8BhCt/9d3z6g6hJCdv0z6Ee4bioQ24HjR7VU3E8qXNW59GX7twoOZ7V46KO0Yvdlx2v5t5/pj5Ai9UbPAWtgLwG0BM5eYOnIbyBRZTqDawirsrbCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Deal = _t, #"Process Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Deal", type text}, {"Process Time", type duration}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Total", each List.Sum([Process Time]), type nullable duration}})
in
    #"Grouped Rows"