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 all,
Need some help for the following.
Below is a simple table.
My question is as follows: how can I calculate the AVERAGE of the TIME_TO_COMPLETE_1 values per day?
Thanks!
John
Solved! Go to Solution.
You can change the measure to be as follows:
AverageTimeConsumption =
VAR Days = INT ( AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) )
VAR HoursValue = ( AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) - Days ) * 24
VAR Hours = INT ( HoursValue )
VAR MinsValue = ( HoursValue - Hours ) * 60
VAR Mins = INT ( MinsValue )
VAR Seconds = ROUND ( ( MinsValue - Mins ) * 60, 0 )
RETURN
IF (
AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) <> BLANK (),
FORMAT ( Days, "00" ) & ":"
& FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Mins, "00" ) & ":"
& FORMAT ( Seconds, "00" )
)
Credit goes to Philip Treacy who wrote a clear article on this topic.
You need to use only a simple average formula if you have a calendar table.
Please check this sample file
Hi @Mohammad_Refaei,
I looked at your sample file, which is clear to me (I have a calender table).
Last question: the outcome of the measure is a decimal number, where I want to see TIME.
Any tip how to convert?
Thanks so far!
John
You can change the measure to be as follows:
AverageTimeConsumption =
VAR Days = INT ( AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) )
VAR HoursValue = ( AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) - Days ) * 24
VAR Hours = INT ( HoursValue )
VAR MinsValue = ( HoursValue - Hours ) * 60
VAR Mins = INT ( MinsValue )
VAR Seconds = ROUND ( ( MinsValue - Mins ) * 60, 0 )
RETURN
IF (
AVERAGEX ( 'Table', 'Table'[time_to_complete_1] ) <> BLANK (),
FORMAT ( Days, "00" ) & ":"
& FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Mins, "00" ) & ":"
& FORMAT ( Seconds, "00" )
)
Credit goes to Philip Treacy who wrote a clear article on this topic.
Hi john
firstly do you have a date table? I can see you have a datetime field, if you wanting to calculate per day its probably best to create new column with only the date values not the time values. You also need a date table, this is to manage the continuity of the date. you would join the date table to your table. You could then plot at a day level the average (time_to_complete)
if you share sample data it would be easy to do a mockup but you have only provided a picture which is not possible to import into an example, you would need to provide text.
Proud to be a Super User!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |