Forum Discussion
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
Trosa_220568
You can create a measure as follows:Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")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)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
6 Replies
Trosa_220568
You can create a measure as follows:Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")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"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)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
