Power BI

Why Your DAX AVERAGEX is Wrong & How to Fix It

Struggling with incorrect AVERAGEX results in Power BI? This guide explains the common pitfalls of row context and shows you how to fix your DAX formulas for good.

Marco Jensen

Microsoft Certified Data Analyst specializing in DAX optimization and advanced Power BI modeling.

September 12, 20256 min read13 views

You’ve been there. You’ve spent hours cleaning your data, building the perfect data model, and designing a stunning Power BI report. Your visuals are crisp, your slicers are responsive, and your total sales figures are spot on. But then you see it—the one card that makes your stomach drop: "Average Sale Per Customer." The number is bizarrely low, completely out of sync with reality. You dive back into your DAX, staring at the formula. It looks so simple, so logical. It has to be right.

Average Sale = AVERAGEX(Sales, Sales[SaleAmount])

It’s elegant, clean, and utterly wrong. If this scenario feels familiar, you’re not alone. This is one of the most common—and frustrating—hurdles in the DAX learning journey. The good news is that the fix is simple, and understanding why it's wrong will unlock a deeper level of DAX mastery.

The Common Trap: Misunderstanding the Iteration

At first glance, the formula above seems to say, "Go through the sales table and average up the sale amounts." And in a way, it does. But it’s doing it in the most literal, unhelpful way possible. Functions ending in "X" in DAX are iterators. They go through a table you provide, row by agonizing row, and perform a calculation.

When you write AVERAGEX(Sales, Sales[SaleAmount]), here’s what Power BI does:

It looks at the first argument: the Sales table. This is its list of tasks.
It goes to the very first row of the Sales table.
It evaluates the second argument, Sales[SaleAmount], in the context of that single row. The result is just the value from the 'SaleAmount' column in that specific row.
It moves to the second row and does the same thing. Then the third, and so on.
After iterating through every single row, it takes all those individual sale amounts and calculates the average.

What you’ve effectively written is a very fancy, long-winded way of writing AVERAGE(Sales[SaleAmount]). You're calculating the average size of a single transaction, not the average total sales per customer.

The Real Question: Average 'Per What?'

This brings us to the core of the problem. Your business question isn't "What is the average transaction value?" It's "What is the average total spending for a customer?"

The key phrase is "per customer."

This tells you exactly what table your iterator should be walking through. You don't want to iterate over every sale; you want to iterate over every customer. For each customer, you need to calculate their total sales, and then you average those totals.

Let's fix our DAX with this new mindset.

The First Fix: Iterating Over the Right Table

Assuming you have a 'Customers' dimension table, the correct approach looks like this:

Avg Sales Per Customer = AVERAGEX(Customers, [Total Sales])

Wait, what is [Total Sales]? That’s a separate, fundamental measure you should always have in your model:

Total Sales = SUM(Sales[SaleAmount])

Let's break down why this new formula works so beautifully.

AVERAGEX looks at its first argument: the Customers table. Now, its to-do list is a unique list of your customers.
It goes to the first customer, say, "Customer A."
It then evaluates the expression [Total Sales]. Here's where the magic happens. The row context (we're on "Customer A") is transformed into an equivalent filter context. This is called context transition. The [Total Sales] measure is now calculated as if someone had applied a filter for "Customer A." It sums up all sales for that one customer.
Let's say the total for Customer A is $1,500. AVERAGEX stores this value.
It then moves to "Customer B," calculates their total sales (e.g., $800), and stores that.
It continues this for every single customer.
Finally, it averages all the stored values ($1,500, $800, ...), giving you the true average sales per customer.

The Bulletproof Solution: Using VALUES

The method above is great if you have a clean 'Customers' table. But what if you want to be more flexible? What if your customer information is directly in your sales table? Or what if you want to calculate the average sales per Product Category, which also exists in the sales table?

This is where the VALUES function becomes your best friend. VALUES takes a column and returns a unique, single-column table of its values.

Here’s the most robust and common pattern for solving this problem:

Avg Sales Per Customer (Robust) = AVERAGEX( VALUES(Sales[CustomerID]), [Total Sales] )

This formula is brilliant. Instead of relying on a separate dimension table, it dynamically creates the table to iterate over. It says, "Give me a unique list of all CustomerIDs that exist in the Sales table, and then for each one of those IDs, calculate the [Total Sales]."

Why VALUES is So Powerful

The VALUES pattern is superior for a few reasons:

It's self-contained: It only uses the sales table itself to determine the list of customers.
It's context-aware: If you filter your report by a year, say 2024, VALUES(Sales[CustomerID]) will only return a list of customers who made a purchase *in 2024*. Your average will dynamically update to be "Average Sales Per Customer who shopped in 2024." This is incredibly powerful.
It's flexible: Want to find the average sales per region? Just swap out the column: AVERAGEX(VALUES(Sales[Region]), [Total Sales]). It’s a plug-and-play pattern.

Bringing It All Together

Let's visualize the difference. Imagine this simple sales data:

InvoiceID	CustomerID	SaleAmount
1	CUST-A	$100
2	CUST-B	$50
3	CUST-A	$300
4	CUST-C	$1000
5	CUST-B	$75

The Wrong Way: AVERAGEX(Sales, Sales[SaleAmount])

This would average the values in the `SaleAmount` column: ($100 + $50 + $300 + $1000 + $75) / 5 = $305. This is the average transaction size.

The Right Way: AVERAGEX(VALUES(Sales[CustomerID]), [Total Sales])

VALUES(Sales[CustomerID]) creates a temporary table: {CUST-A, CUST-B, CUST-C}.
For CUST-A, [Total Sales] is $100 + $300 = $400.
For CUST-B, [Total Sales] is $50 + $75 = $125.
For CUST-C, [Total Sales] is $1000.
AVERAGEX then averages those results: ($400 + $125 + $1000) / 3 = $508.33.

The results—$305 vs. $508.33—tell completely different stories. Only the second one answers the question you were actually asking.

Your New DAX Mantra

From now on, whenever you reach for an iterator function like AVERAGEX, stop and ask yourself that one simple question: "Per what?"

The answer to that question is the table—or the VALUES(column)—that belongs in the first argument of your function. It’s a small mental shift that separates frustrating, incorrect DAX from powerful, insightful analysis. Stop blaming AVERAGEX; it’s just doing what you tell it to. By understanding how to give it the right instructions, you'll turn this common point of failure into one of the most reliable tools in your DAX arsenal.