Introduction
When working with numerical data in Power BI, calculating averages is a common requirement. While DAX provides two functions — AVERAGE() and AVERAGEX() — many developers get confused about when to use which one.
In this article, we’ll clearly understand:
What is AVERAGE() in Power BI?
AVERAGE() calculates the average of a single numeric column.
Syntax:
AVERAGE(<column>)
Example:
Suppose you have a Sales table:
| Order ID | Sales Amount |
|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
Measure:
Average Sales = AVERAGE(Sales[Sales Amount])
Result:
(100 + 200 + 300) / 3 = 200
Note to remember:
AVERAGE() works directly on a column — no row-by-row calculation.
What is AVERAGEX() in Power BI?
AVERAGEX() is an iterator function.
Syntax:
AVERAGEX(<table>, <expression>)
Practical Business Example (Very Important)
Scenario: You want to calculate Average Profit per Order.
Your table:
| Order ID | Sales | Cost |
|---|
| 1 | 500 | 300 |
| 2 | 400 | 250 |
| 3 | 600 | 450 |
Step 1: Calculate Profit per Row
Profit = Sales – Cost
| Order ID | Profit |
|---|
| 1 | 200 |
| 2 | 150 |
| 3 | 150 |
Wrong approach (Using AVERAGE):
You cannot write:
AVERAGE(Sales[Sales] - Sales[Cost])
Because AVERAGE() only accepts a column — not an expression.
Correct approach using AVERAGEX:
Average Profit Per Order =
AVERAGEX(
Sales,
Sales[Sales] - Sales[Cost]
)
How it works:
Iterates row 1 → 500 - 300 = 200
Iterates row 2 → 400 - 250 = 150
Iterates row 3 → 600 - 450 = 150
Averages results:
(200 + 150 + 150) / 3 = 166.67
Key Difference Between AVERAGE and AVERAGEX
| Feature | AVERAGE | AVERAGEX |
|---|
| Works on | Single column | Table + Expression |
| Supports row calculations | No | Yes |
| Is iterator function | No | Yes |
| Performance | Faster | Slightly slower (row iteration) |
| Use case | Simple column average | Calculated average per row |
When Should You Use AVERAGEX()?
Use AVERAGEX() when:
You need to calculate average of a formula
You need row-by-row logic
You are combining multiple columns
You are applying filter context inside calculation
Example 2: Average Sales Per Customer
If you want: Total Sales per Customer, then average of those totals.
Average_Sales_Per_Customer =
AVERAGEX(
VALUES(Sales[CustomerID]),
CALCULATE(SUM(Sales[SalesAmount]))
)
Here:
VALUES() creates a virtual table of unique customers
For each customer, we calculate total sales
Then average those totals
This is impossible using simple AVERAGE().
Performance Note
If you only need average of a column, use AVERAGE()
If calculation per row is required, use AVERAGEX()
Avoid unnecessary iterator functions for performance optimization
Common Mistake Developers Make
Many developers write:
AVERAGEX(Sales, Sales[SalesAmount])
This gives the same result as:
AVERAGE(Sales[SalesAmount])
In such cases, AVERAGEX() is unnecessary and less efficient.
Final Summary
AVERAGE() → Simple column average
AVERAGEX() → Average of calculated expression per row
If calculation involves multiple columns, always use AVERAGEX()
Conclusion
Understanding the difference between AVERAGE() and AVERAGEX() helps you write optimized, accurate DAX measures and avoid common logical mistakes in business calculations. Choosing the right function ensures both performance efficiency and analytical precision in your Power BI reports.