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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!