cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwi1
Post Patron
Post Patron

calculate average time consumption

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

 

Knipsel.JPG

1 ACCEPTED SOLUTION
Mohammad_Refaei
Solution Specialist
Solution Specialist

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.

View solution in original post

5 REPLIES 5
Mohammad_Refaei
Solution Specialist
Solution Specialist

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

 

Mohammad_Refaei
Solution Specialist
Solution Specialist

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.

View solution in original post

perfect, thanks @Mohammad_Refaei !

vanessafvg
Super User
Super User

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.