AVERAGEX
Evaluates an expression on each row of a table, then averages the results.
AVERAGEX(<table>, <expression>)Returns: A number (a scalar). BLANK when there are no values to average.
What it does
AVERAGEX works like SUMX, but it divides the total by the number of rows instead of returning the total. It walks a table, calculates the expression on each row, and averages the answers. Use it when the average must be taken across rows that are calculated, or across a list of customers, accounts or products that you build with VALUES or FILTER.
Syntax and arguments
- table: the rows to average, such as
Sales,VALUES(Sales[CustomerKey])or a filtered table. - expression: the value calculated for each row. Measures are allowed here, and each one is evaluated for that row.
Example
The sales director wants the average gross margin earned from each customer who bought in the selected period, not the margin of the average customer's order.
Avg Margin per Customer =
AVERAGEX(
VALUES(Sales[CustomerKey]),
[Gross Margin]
)
VALUES builds one row per customer in the current filter context. For each customer, [Gross Margin] is evaluated for that customer alone. AVERAGEX then averages those per customer figures, so every customer counts once.
Example: a second, different use
The average price per sales line, where each line is priced separately:
Avg Price per Line =
AVERAGEX(
Sales,
DIVIDE(Sales[SalesAmount], Sales[SalesQuantity])
)
Every line counts once, regardless of its size. Dividing total sales by total quantity would instead give a weighted average in which large orders matter more.
Things to watch
- Each row counts equally. A customer who bought one item counts as much as a customer who bought a thousand, unless the expression is weighted.
- Blank results are skipped, so a customer with no margin does not pull the average down.
- Measures inside AVERAGEX are evaluated per row, which can be slow over large lists. Keep the list of rows small with a filter when you can.
- Use DIVIDE inside the expression so a row with zero quantity does not stop the whole calculation.
- AVERAGE takes a column. If you need a calculation on each row, AVERAGEX is the right choice.
In business terms
Finance teams use AVERAGEX for average margin per account, average revenue per active client, average cost per employee and average days to collect per invoice. It answers "what does a typical customer look like," which a single total cannot.