How to Use AVERAGEX in Power BI

Performing row-by-row calculations is a foundational skill in data analysis, and Power BI’s AVERAGEX function is the perfect tool for the job. Unlike its simpler cousin, AVERAGE, the AVERAGEX function unlocks a more powerful and flexible way to find averages in your data. In this tutorial, we’ll walk you through exactly how AVERAGEX works, why it’s different from AVERAGE, and how to use it in your reports with practical, step-by-step examples.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Is AVERAGEX? And How Is It Different From AVERAGE?

At first glance, AVERAGEX and AVERAGE seem like they do the same thing. They both calculate an average, but how they do it is completely different. Understanding this distinction is the key to mastering more advanced calculations in DAX (Data Analysis Expressions), the formula language of Power BI.

The AVERAGE Function: Simple and Straightforward

The AVERAGE function is a simple aggregator. It works just like the average function in Excel. You give it a single column, and it calculates the arithmetic mean of all the numbers in that column.

The syntax is basic:

AVERAGE(<columnName>)

For example, if you had a sales table with a column named [SalesAmount], you could find the average of all sales with this measure:

Avg Sales = AVERAGE('Sales'[SalesAmount])

It adds up all the values in the SalesAmount column and divides by the total number of rows. Easy. But its simplicity is also its biggest limitation - it can only operate on one column at a time.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The AVERAGEX Function: Powerful and Flexible

The AVERAGEX function is an iterator function. The "X" at the end signals that it iterates, or goes through a table, one row at a time. For each row, it evaluates an expression that you define. After it has calculated the result of that expression for every single row, it then takes all those individual results and calculates their average.

This row-by-row capability is what makes it so powerful. It lets you create complex calculations that involve multiple columns.

The syntax looks like this:

AVERAGEX(<table>, <expression>)

• <table>: The table that the function will "iterate" or loop through.
• <expression>: The calculation to perform for each row of the specified table.

Let's make this concrete with a scenario where AVERAGE just won't work.

Example 1: Calculating Average Sales Per Transaction

Imagine your Sales table doesn't have a TotalSales column. Instead, it has Quantity and UnitPrice columns. Your goal is to find the average revenue for each transaction.

You might be tempted to do this:

Incorrect Average = AVERAGE('Sales'[Quantity]) * AVERAGE('Sales'[UnitPrice])

This is mathematically incorrect and will give you the wrong answer. It calculates the average quantity across all transactions and multiplies it by the average unit price across all transactions. This is a classic "average of averages" problem that leads to misleading results.

To get the right answer, you need to first calculate the total sales for each row (Quantity * UnitPrice) and then average those totals. This is exactly what AVERAGEX was built for.

Step-by-Step Instructions:

1. In Power BI Desktop, right-click on your sales table in the Fields pane and select New Measure.
2. In the formula bar, type the following DAX formula:

Average Sales Per Transaction = 
AVERAGEX(
    'Sales', 
    'Sales'[Quantity] * 'Sales'[UnitPrice]
)

1. Press Enter to create the measure.

How It Works:

• AVERAGEX('Sales', ...): This tells Power BI to look at the Sales table and get ready to go through it row by row.
• 'Sales'[Quantity] * 'Sales'[UnitPrice]: For the very first row, it takes the value from the Quantity column and multiplies it by the value in the UnitPrice column. Let's say that's 2 * $10, which equals $20.
• It then moves to the second row and does the same calculation (e.g., 3 * $25 = $75).
• This process repeats for every single row in the entire Sales table.
• Once it has a list of the multiplication results for all rows ($20, $75, etc.), AVERAGEX calculates the average of that list.

Now, you can drop this measure onto a Card visual to see the true average sales value per transaction. This simple but powerful pattern is one you will use constantly in your Power BI reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 2: Analyzing Average Profitability

Let's take it a step further. Iterators are fantastic for more advanced metrics like profitability. Suppose your Sales table has Cost and Revenue columns. You want to calculate the average profit margin across all your transactions.

Like before, simply averaging the columns and trying to combine them afterward won't work. You need to calculate the margin for each transaction individually.

Step-by-Step Instructions:

1. Create another New Measure in your Sales table.
2. Enter this DAX formula:

Average Profit Margin =
AVERAGEX(
    'Sales',
    ('Sales'[Revenue] - 'Sales'[Cost]) / 'Sales'[Revenue]
)

1. To make it even better and avoid potential "division by zero" errors, you can use the DIVIDE function:

Average Profit Margin =
AVERAGEX(
    'Sales',
    DIVIDE(
        'Sales'[Revenue] - 'Sales'[Cost],
        'Sales'[Revenue]
    )
)

How It Works In a Visual:

When you use this measure in a table or chart, its real power becomes apparent. Drag a Table visual onto your canvas. Add the Product Category from a related Products table, and then add your [Average Profit Margin] measure.

Power BI now automatically applies a "filter context." When the table shows the "Electronics" category, the AVERAGEX function doesn't run on the entire Sales table. Instead, it only iterates over the sales rows that are related to the Electronics category, giving you the precise average profit margin for just those products.

This dynamic calculation is what separates static Excel reports from interactive Power BI dashboards.

Supercharge AVERAGEX with Virtual Tables Using FILTER

The first argument in AVERAGEX is a table, but it doesn't have to be a physical table from your data model. It can also be a virtual table that you create on the fly with another DAX function. This is where you can start performing some very specific and powerful analysis.

Scenario: You want to calculate the average sales per transaction, but only for products sold in the "North America" region. You don't want to rely on a slicer - you want a dedicated measure for it.

For this, you can combine AVERAGEX with the FILTER function.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions:

1. Create a New Measure.
2. Enter the following DAX formula:

Avg Sales North America =
AVERAGEX(
    FILTER(
        'Sales', 'Sales'[Region] = "North America"
    ),
    'Sales'[Quantity] * 'Sales'[UnitPrice]
)

How It Works:

• FILTER('Sales', 'Sales'[Region] = "North America"): This part runs first. It scans the Sales table and creates a temporary, virtual table in memory. This virtual table contains only the rows where the value in the [Region] column is "North America."
• AVERAGEX(...): Now, AVERAGEX takes this new, pre-filtered table and iterates over it row by row.
• 'Sales'[Quantity] * 'Sales'[UnitPrice]: The expression is calculated for each row in the filtered virtual table.
• Finally, it averages the results. This gives you a hard-coded measure that will always show the average for North America, regardless of what other slicers are selected on the report page. This is incredibly useful for creating reports that compare performance across different segments (e.g., a chart comparing [Avg Sales North America] vs. [Avg Sales Europe]).

Common Mistakes and Best Practices

As you use AVERAGEX, keep these tips in mind to avoid common pitfalls:

• Know when to use AVERAGE vs. AVERAGEX: If you're just averaging a single, straightforward column, use AVERAGE. If your calculation involves multiple columns per row or a complex condition, reach for AVERAGEX.
• Use variables for readability: For longer formulas, use VAR to define parts of your calculation. It makes your DAX easier to read, debug, and maintain.

Avg Sales with Variables =
VAR RevenuePerRow = 'Sales'[Quantity] * 'Sales'[UnitPrice]
RETURN
AVERAGEX(
    'Sales', 
    RevenuePerRow
)

• Mind your performance: Iterator functions work row by row, so on tables with tens of millions of rows, they can be slower than simple aggregations. Always test the performance of calculations on large datasets.

Final Thoughts

Moving from AVERAGE to AVERAGEX is a major step in mastering DAX. It opens the door to performing sophisticated calculations at the row level, allowing you to create meaningful metrics like weighted averages, profitability margins, and conditional summaries that go far beyond basic aggregations and bring immense value to your reports.

Of course, learning the intricacies of DAX, managing filter contexts, and debugging complex formulas can take a lot of time. We built Graphed because we believe valuable insights shouldn't be locked behind a steep learning curve. Instead of wrestling with syntax, you can connect your data and just ask a question in plain English, like "What was our average profit margin by product category last quarter?" We instantly build the analysis for you in a live, interactive dashboard, letting you focus on the insights instead of the formulas.