cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculate average time per day

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

3 ACCEPTED SOLUTIONS
Super User

@Trosa_220568

You can create a measure as follows:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Community Champion

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)

Community Support

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

6 REPLIES 6
Community Support

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

Community Champion

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)

Super User

@Trosa_220568

You can create a measure as follows:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

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

Regular Visitor

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

Community Champion
``````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"``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.