Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Trosa_220568
You can create a measure as follows:
Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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
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)
@Trosa_220568
You can create a measure as follows:
Average Process Time = FORMAT( AVERAGE( Table1[Process Time] ), "hh:mm:ss")
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
How do I get this to work and apply any filters I have?
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!
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |