How to Calculate Average Time in Power BI

Calculating an average time seems simple, but in Power BI, it often trips people up. Because Power BI stores time as a decimal number, a standard AVERAGE function can return confusing results. This guide will walk you through the correct methods for calculating and formatting average time using DAX, ensuring you get accurate, readable results every time.

Why Can't I Just Use the AVERAGE Function?

Before jumping into the solutions, it's helpful to understand what’s happening behind the scenes. Power BI, like Excel, uses a serial number system to handle dates and times. A full day (24 hours) is represented by the number 1. Time is the fractional part of that number.

• 12:00 AM (midnight) is 0.0
• 6:00 AM is 0.25
• 12:00 PM (noon) is 0.5
• 6:00 PM is 0.75

When you use the standard AVERAGE() function on a column full of time values (e.g., call durations or ticket response times), Power BI averages these underlying decimal numbers. For example, the average of 6:00 AM (0.25) and 12:00 PM (0.5) is 0.375. If you try to display this, Power BI might show it as 9:00 AM, but applying this logic across hundreds of values often leads to formatting issues or results that don't make sense as a duration.

To do it correctly, you need to convert your times into a consistent unit you can average - like total seconds - and then format that average back into a familiar time format like hh:mm:ss.

Step 1: Get Your Data Ready in Power Query

Clean data is the foundation of any good analysis. Before writing any DAX, let's make sure your time column is set up correctly in the Power Query Editor.

1. In Power BI Desktop, click on Transform data from the Home ribbon. This opens the Power Query Editor.
2. Find your time column. This might be named something like 'Call Duration', 'Time to Resolution', or 'Session Length'.
3. Check the data type. Click on the icon next to the column header (it might show "ABC", "123", or a calendar). Make sure the data type is set to Time or Date/Time. If it's set to Text, Power BI won't be able to perform calculations on it. Once you’ve confirmed the data type is correct, click Close & Apply on the top-left to save your changes.

Step 2: The Best Method - Convert Time to Seconds for Calculation

The most reliable and versatile way to calculate average time is to first convert all time values into a single, straightforward unit: seconds. From there, you can average them and format the result however you like.

This process involves three parts: creating an initial calculation in a column, defining the average in a measure, and then formatting that measure for display.

Part A: Create a Calculated Column for Total Seconds

First, we need to add a new column to our data table that translates each time value into the total number of seconds it represents.

1. Go back to the Report view in Power BI. Select the table containing your time data from the Data pane on the right.
2. On the Table tools ribbon that appears at the top, click New column.
3. Enter the following DAX formula in the formula bar. Replace 'YourTable'[YourTimeColumn] with the actual names of your table and time column.

`Total Seconds = HOUR('YourTable'[YourTimeColumn]) * 3600

• MINUTE('YourTable'[YourTimeColumn]) * 60
• SECOND('YourTable'[YourTimeColumn])`

Let's break that down:

• HOUR('YourTable'[YourTimeColumn]) extracts the hour part of the time and multiplies it by 3600 (the number of seconds in an hour).
• MINUTE('YourTable'[YourTimeColumn]) extracts the minute part and multiplies it by 60.
• SECOND('YourTable'[YourTimeColumn]) extracts the seconds.

Adding these together gives you the total duration in seconds. For a time of 01:10:05, this formula would calculate (1 * 3600) + (10 * 60) + 5 = 4205 seconds.

Part B: Create a Measure to Calculate the Average Seconds

Now that you have your total seconds for each row, you can easily average them using a measure. Using a measure is best practice because it's dynamic and calculated in real-time based on the filters applied in your report.

1. On the Home ribbon, click New measure.
2. Type in the following DAX formula:

Average Seconds = AVERAGE('YourTable'[Total Seconds])

This measure simply averages all the values in our newly created 'Total Seconds' column. This gives you a single numerical value, for example, 530.5. This number is accurate, but it’s not very user-friendly.

Part C: Create a Measure to Format the Result

The final step is to convert the average number of seconds back into a readable time format. We'll do this with one more measure.

1. Click New measure again.
2. Enter this DAX formula. This is a common and robust pattern used by Power BI experts to format time durations correctly.

Average Time Formatted = VAR TotalSeconds = [Average Seconds] // This calls our previous measure VAR Hours = INT ( TotalSeconds / 3600 ) VAR Minutes = INT ( MOD ( TotalSeconds, 3600 ) / 60 ) VAR Seconds = ROUNDUP ( MOD ( TotalSeconds, 60 ), 0 ) RETURN FORMAT ( Hours, "00" ) & ":" & FORMAT ( Minutes, "00" ) & ":" & FORMAT ( Seconds, "00" )

What’s happening here?

• We start by declaring variables using VAR to make the formula easier to read and more efficient.
• TotalSeconds simply grabs the value from our Average Seconds measure.
• Hours divides the total seconds by 3600 and takes the integer part (e.g., 4205 / 3600 = 1.168..., so INT gives us 1).
• Minutes uses the MOD function to find the remaining seconds after the hours are accounted for, then divides by 60 to get the minutes.
• Seconds uses MOD to find the remainder after dividing by 60. We use ROUNDUP to handle any decimal residuals cleanly.
• Finally, the RETURN statement uses the FORMAT(..., "00") function to ensure each part has a leading zero if needed (e.g., 7 becomes "07") and combines them with colons.

Now you can drag this Average Time Formatted measure into a Card visual or a table in your report to display a clean, perfect average time like 00:08:51.

Alternative Method: Using AVERAGEX Without a Helper Column

If you prefer to avoid creating an extra calculated column in your data model, you can accomplish the same thing using the AVERAGEX function inside a single measure. This has the benefit of keeping logic contained within your measures pane but can feel slightly less step-by-step for beginners.

AVERAGEX is an iterator function, which means it goes through a specified table row by row, performs a calculation for each row, and then averages the results of those calculations.

1. Click New measure in the Home ribbon.
2. Enter the following DAX code:

Average Time with AVERAGEX = // First, calculate the average time as a decimal // For example, 6 AM = 0.25, 12 PM = 0.5, average = 0.375 (9 AM) VAR avgDecimal = AVERAGEX( 'YourTable', 'YourTable'[YourTimeColumn] ) // Then, format the resulting decimal back into a time string RETURN FORMAT(avgDecimal,"hh:mm:ss")

Here's how it works:

• The AVERAGEX function iterates over 'YourTable'. For each row, it evaluates the expression, which is simply the time value itself ('YourTable'[YourTimeColumn]).
• It effectively averages the underlying decimal serial numbers we talked about at the beginning.
• The FORMAT function then takes this final averaged decimal (e.g., 0.375) and converts it into the text string "09:00:00" using the "hh:mm:ss" format code.

This method is quick and clean, especially for straightforward time-of-day calculations. However, for durations that could exceed 24 hours, the seconds conversion method is generally more flexible and easier to debug.

Putting It All Together: An Example

Let's imagine you have a customer support table with a [Task Duration] column that records how long it took to solve a ticket. It has three entries:

• Ticket A: 00:15:30
• Ticket B: 00:45:10
• Ticket C: 01:10:05

Using the seconds-conversion method:

1. The Total Seconds calculated column would produce:
2. The Average Seconds measure would calculate: (930 + 2710 + 4205) / 3 = 2615 seconds
3. The Average Time Formatted measure would take 2615 seconds and do the final calculation:

The final returned value displayed in your report would be 00:43:35.

Final Thoughts

Working with time durations in Power BI simply requires a bit of DAX to translate them into a purely numeric format for calculation and then convert them back into a human-readable string for display. Breaking time down into total seconds is a robust technique that gives you full control over your analysis and formatting.

While mastering DAX is a powerful skill, we know it can feel like a steep learning curve when you just need a quick answer. That's why we built Graphed. Instead of setting up complex measures and helper columns, you can simply ask questions in plain English, like "What was our average task duration last month?" and get an instant, properly formatted dashboard. Graphed automates the difficult data wrangling so you can analyze your reports right away.