How to Calculate Average in Power BI DAX

Calculating an average seems simple enough, but once you move from a spreadsheet into a Power BI report, you quickly find that it involves a little more nuance. Knowing which DAX function to use - and when - is the key to getting accurate, meaningful insights from your data. This guide will walk you through how to calculate an average in Power BI using the two most important DAX functions for the job: AVERAGE and AVERAGEX.

Understanding the Basics: The AVERAGE Function

The simplest way to calculate an average in Power BI is by using the AVERAGE function. It does exactly what its name suggests: it finds the arithmetic mean of all the numbers in a single, specified column.

How it Works

The syntax for this function is straightforward:

AVERAGE(<column>,)

You just tell Power BI which column you want to average, and it takes it from there. The function adds up all the numeric values in that column and then divides by the count of those values. It automatically ignores blank cells and any cells containing text, which prevents your calculations from throwing errors.

Step-by-Step Example: Calculating Average Product Price

Let's imagine you have a simple table called 'Products' with a column for 'Product Name' and another for 'Price'. You want to display the average price of all your products on a report.

1. Create a New Measure. In Power BI Desktop, right-click on your 'Products' table in the Fields pane and select "New measure." This will open the formula bar.
2. Write the DAX Formula. Type the following formula into the bar. We give our measure a descriptive name, like "Average Product Price," to make it easy to find later.

Average Product Price = AVERAGE(Products[Price])

Here, Products[Price] tells Power BI to look at the 'Products' table and find the 'Price' column.

1. Use Your New Measure. Once you hit Enter, the measure will appear in your Fields pane. You can now drag and drop it onto your report canvas. A great way to display a single metric like this is by using a Card visual, which will show just the final calculated value.

That's it! You've successfully calculated the average price. This approach is perfect for any scenario where you just need a straightforward average of an existing column.

Going a Step Further: When to Use AVERAGEX

The AVERAGE function is great for simple cases, but business data is rarely that simple. What happens when the value you want to average doesn't exist as a single column? For instance, what if you need to find the average revenue per transaction, but you only have 'Quantity Sold' and 'Price per Unit' columns?

This is where AVERAGEX comes in. It's a more powerful "iterator" function. Instead of just looking at one column, AVERAGEX goes through a table row by row, performs a calculation for each row that you define, and then finds the average of the results of those calculations.

How it Works

The "X" at the end of functions like AVERAGEX, SUMX, or COUNTX signifies that it's an iterator. They all follow a similar syntax pattern:

AVERAGEX(<table>, <expression>)

• <table>: The table that the function will "iterate" or loop through, row by row.
• <expression>: The calculation that will be performed for each individual row in that table.

Step-by-Step Example: Calculating Average Transaction Value

Let's use a 'Sales' table with multiple columns, including 'Product ID', 'Quantity Sold', and 'Price per Unit'. Our table does not have a pre-calculated 'Total Revenue' column for each sale. Our goal is to find the average value of each transaction.

1. Create a New Measure. Right-click your 'Sales' table in the Fields pane and select "New measure."
2. Write the AVERAGEX Formula. Now, let's create the expression. We need to tell Power BI to go through the 'Sales' table, and for each row, multiply the 'Quantity Sold' by the 'Price per Unit'.

Average Transaction Value = AVERAGEX( Sales, Sales[Quantity Sold] * Sales[Price per Unit] )

Let's break down what's happening here:

• AVERAGEX(Sales, ...) tells Power BI to perform a row-by-row operation on the 'Sales' table.
• Sales[Quantity Sold] * Sales[Price per Unit] calculates the total value for each transaction.

After completing this multiplication for every row, it takes all of those individual results and calculates the average.

1. Add the Measure to Your Report. Drag the new "Average Transaction Value" measure onto your canvas, perhaps into another Card visual. You now have your average, calculated dynamically without needing to add a new column to your source data.

This dynamic calculation is incredibly flexible. You can create complex expressions that reference multiple columns or even other measures, giving you much more control over your analysis.

AVERAGE vs. AVERAGEX: Which One Should You Choose?

Understanding the difference helps you build more efficient and accurate reports. Here’s a simple cheat sheet to help you decide when to use each function.

Use AVERAGE when:

• You need to find the simple arithmetic mean of a single, existing numeric column.
• Your source data already contains the column you want to analyze (e.g., a 'Sales Amount' column).
• You are looking for the fastest and most direct way to get a simple aggregation.

Use AVERAGEX when:

• The number you need to average is the result of a calculation involving two or more columns.
• You need to perform a calculation before the averaging can happen (e.g., first calculate revenue per transaction, then average those revenues).
• You need to apply some logic using other functions, like FILTER. For example, calculating the average sales of transactions over $50.
• You need more advanced, row-level control over your calculation.

Practical Tips & Common Pitfalls

As you use these functions more, there are a few concepts to keep in mind that will help you avoid common issues.

1. Always Remember Filter Context

Every calculation in Power BI happens within a "filter context." This means that the result of your measure will change based on the filters applied in your report. For example, your 'Average Transaction Value' measure will show one number for the entire dataset. But if you put it in a table visual next to product categories, it will display the average transaction value for each category individually. If you click on "January" in a slicer, the value will update to show the average for just that month. This is the power of dynamic reporting in Power BI — your measures react to user interaction.

2. Be Mindful of Blanks and Zeros

AVERAGE conveniently ignores text and blank cells. However, it does include zeros in its calculation. In most cases, this is what you want. But if you have transactional data where a '0' indicates no sale rather than a free item, you may want to exclude these rows. You can do this within an AVERAGEX function by wrapping your table in a FILTER:

Average Sales (Excluding Zeros) = AVERAGEX( FILTER(Sales, Sales[Sales Amount] > 0), Sales[Sales Amount] )

3. Don't Confuse Measures with Calculated Columns

You could technically create a new calculated column in your table for 'Total Revenue' (= Sales[Quantity Sold] * Sales[Price per Unit]) and then just use a simple AVERAGE on that new column. While this works, it's often not the best practice.

• Calculated Columns are computed when the data is loaded and use up memory (RAM). They are static and only recalculate on a full data refresh.
• Measures are calculated "on the fly" when a user interacts with a report. They don't store data in the same way and are generally more efficient for aggregation work like this.

For finding an average across a dataset, using an AVERAGEX measure is almost always a better, more memory-efficient choice than creating a helper column first.

Final Thoughts

Learning how to calculate an average in Power BI DAX comes down to understanding the distinction between AVERAGE for simple columns and AVERAGEX for complex, row-by-row expressions. With AVERAGE handling direct summaries and AVERAGEX providing the flexibility for more detailed calculations, you're well-equipped to analyze your data and uncover valuable performance metrics in your reports.

While mastering DAX is a powerful skill, we know the learning curve can be steep and time-consuming, preventing team members from getting the answers they need. We built Graphed to solve exactly this problem. Instead of wrestling with formulas, you can simply connect your data sources — like Google Analytics, Shopify, or your CRM — and use plain English to build real-time dashboards and reports. Just ask, "what was our average transaction value last month?" and get an instant visualization without writing a single line of DAX.