The request
From Steven Clark, Head of E-commerce
“I keep hearing the app brings smaller baskets than the web. Prove it either way: average order value per channel, completed orders only. The report already has Sales and Completed Orders.”
Your goal
The matrix has orders[channel] on its rows, next to the given measures Sales (completed sales) and Completed Orders. Create a measure called Average Order Value: Sales divided by Completed Orders, using DIVIDE.
- Fill
[Average Order Value]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = orders[channel]; values = [Sales], [Completed Orders], [Average Order Value]
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")Completed Orders = CALCULATE(COUNTROWS(orders), orders[status] = "completed")
Data pane: tables and columns
- order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
- orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| channel | Sales | Completed Orders | Average Order Value |
|---|---|---|---|
| app | $2,024,727.98 | 3,885 | |
| marketplace | $837,482.83 | 1,597 | |
| web | $3,032,073.63 | 5,367 | |
| Total | $5,894,284.44 | 10,849 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Steven gets, and why
The lesson
Average order value is one of the first numbers any e-commerce manager asks for, and one of the easiest to get subtly wrong. The safe pattern is a ratio of two measures.
What you are working in
This is a practice report built for this site; Power BI Desktop runs only on Windows. The
matrix has orders[channel] on the rows and two measures already in the report:
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Completed Orders = CALCULATE(COUNTROWS(orders), orders[status] = "completed")CALCULATE keeps only completed orders; you’ll learn it properly in the next chapter. Your
measure is checked in every cell, Total included.
In Power BI Desktop: Home → New measure, type the formula, press Enter, and drag it into the matrix’s Values. Set its format to currency under Measure tools → Format.
DIVIDE instead of /
Average Order Value = DIVIDE([Sales], [Completed Orders])DIVIDE(numerator, denominator) does the same as [Sales] / [Completed Orders] with one
difference: when the denominator is zero or blank, it returns a blank instead of an error or
infinity. A third argument sets a different fallback: DIVIDE(a, b, 0).
That matters in real reports. When a cell has sales but a denominator of zero (a count written
to return 0 instead of blank, say), / shows “Infinity”, and 0 divided by 0 shows “NaN”.
DIVIDE leaves the cell empty instead, and Power BI hides rows where every value is blank.
Microsoft’s guidance is to use DIVIDE whenever the denominator could be zero or blank, which
in a filtered report means nearly always.
Why a ratio of totals
An average order value has to be computed at the order level: all the sales in the cell divided by all the orders in the cell. That is exactly what dividing two measures does, in every cell:
- In the web row, web sales over web orders.
- In the Total row, all sales over all orders. Not the average of the three channel values, which would give the small marketplace channel as much weight as the web.
Why not AVERAGE?
AVERAGE(order_items[line_total]) averages order lines. An order with three lines counts
three times, and each line is only part of a basket, so the number is far too small. There is
no column holding one value per order to average, which is why the ratio is the right tool.
Common mistakes
/instead of DIVIDE. Correct here, fragile under filters. The ticket asks for DIVIDE.- Counting all orders.
COUNTROWS(orders)includes cancelled and refunded orders, while Sales doesn’t, so the average comes out too low. - Averaging the rows yourself. The Total is a ratio of totals, not an average of ratios.
Lantern lit!
Practised: measuredivideratioaverage order valuemeasure reference
- Solved
- No hints
- First run