Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |