Level 2 · Average order value with AVERAGEX

Kevin is right to be suspicious. A number called “average order” that is smaller than most baskets usually averages the wrong thing: the lines of the orders instead of the orders.

What you are working in

This is a practice report built for this site. Power BI Desktop runs only on Windows, so a DAX engine in the page evaluates your measure in every cell, following Power BI’s rules. In Power BI Desktop: Home → New measure, type the formula, then add the measure to the matrix next to Sales. The two slicers above the matrix (status = completed, Year Quarter = 2025 Q4) are the practice version of slicers on the report page.

Grain decides what an average means

order_items has one row per order line. AVERAGE(order_items[line_total]) is an honest average of lines: a sofa line and a cushion line count the same. But an order is a basket of lines, and the question “how much does a customer spend per order?” needs one number per order.

AVERAGEX: one number per item of a list, then the average

Avg Order Value =
AVERAGEX(
    VALUES(orders[order_id]),
    CALCULATE(SUM(order_items[line_total]))
)

• VALUES(orders[order_id]) returns the order ids visible in the cell: the web orders of 2025 Q4 that were completed, in the web row.
• AVERAGEX evaluates the second argument once for each id and averages the results, skipping blanks.
• CALCULATE( … ) is what makes “each id” mean something. Inside an iterator, CALCULATE turns the current row into a filter (this is called context transition), so the SUM sees only the lines of that one order. Without it, SUM sees every line in the cell, and every order gets the channel’s whole sales.

Calling a measure gives the same context transition automatically, so AVERAGEX(VALUES(orders[order_id]), [Sales]) also works. Every measure reference has an invisible CALCULATE around it.

Two ways to the same number

Average order value is also total sales divided by the number of orders:

Avg Order Value = DIVIDE([Sales], DISTINCTCOUNT(orders[order_id]))

Here both give the same result, and the division is cheaper. AVERAGEX earns its place when the per-order number is not a plain sum: the average of each order’s item count, of its largest line, of its margin. It also skips orders with a blank total, which a division would still count.

Iterate the smallest table that answers

AVERAGEX over VALUES(orders[order_id]) does one context transition per order. That is fine for a quarter’s orders; over all 30,000 order lines with a measure call on each, a real report gets slow. Iterate a list of the things you are averaging, not the biggest table in the model.

Common mistakes

• AVERAGE of lines. Correct maths, wrong question.
• Forgetting CALCULATE. AVERAGEX(VALUES(orders[order_id]), SUM(order_items[line_total])) repeats the cell’s total for every order, so the “average” equals the channel’s sales.
• Counting rows of orders. The orders table has one row per order, but it is also filtered differently from order lines; for order counts by product, use DISTINCTCOUNT(order_items[order_id]).