Forum Discussion

Trosa_220568's avatar
Trosa_220568
Regular Visitor
5 years ago
Solved

Calculate average time per day

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

DateDealProcess Time
19/07/2021Deal 100:38
19/07/2021Deal 200:42
19/07/2021Deal 300:23
20/07/2021Deal 400:58
20/07/2021Deal 500:17
21/07/2021Deal 601:05
21/07/2021Deal 701:46
22/07/2021Deal 800:27
22/07/2021Deal 900:26
22/07/2021Deal 1002:49
22/07/2021Deal 1100:33
23/07/2021Deal 1201:15
23/07/2021Deal 1300:22
23/07/2021Deal 1400:39
23/07/2021Deal 1500:28

 

Thanks

Need Help

6 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Trosa_220568 

    You can create a measure as follows:

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

     



    • Barryjvr's avatar
      Barryjvr
      Regular Visitor
      3 years ago

      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!

    • BJGRUNDY's avatar
      BJGRUNDY
      Frequent Visitor
      2 years ago

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

       

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago
    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"

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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