The request
From Robert Kim, COO
“For Monday's channel review I want three numbers for 2025, side by side for web, app and marketplace: what we sold, how many orders it took, and what an average order was worth. Completed orders only. The page is already filtered that way; it just needs the measures.”
Your goal
Step 1 of 3. Create a measure called Sales that adds up order_items[line_total], the value of each order line after discount.
- Fill
[Sales]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Below Sales, add a measure called Orders that counts the orders: one row of the orders table is one order.
- Fill
[Orders]on every row of the visualnot done yet - The Total row right toonot done yet
Step 3 of 3. Sales and Orders are now saved in the report and shown in the matrix. Add a third measure, Average Order Value: Sales divided by Orders, written with DIVIDE so an empty cell can never cause an error.
- Fill
[Average Order Value]on every row of the visualnot done yet - The Total row right toonot done yet
Step 1 · your measure
- Visual
- A matrix: rows = orders[channel]; values = [Sales]
- Slicers
- 'Date'[Year] = 2025orders[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 |
|---|---|
| app | |
| marketplace | |
| web | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 2 · your measure
- Visual
- A matrix: rows = orders[channel]; values = [Orders]
- Slicers
- 'Date'[Year] = 2025orders[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 | Orders |
|---|---|
| app | |
| marketplace | |
| web | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 3 · your measure
- Visual
- A matrix: rows = orders[channel]; values = [Sales], [Orders], [Average Order Value]
- Slicers
- 'Date'[Year] = 2025orders[status] = completed
- Measures in the report
Sales = SUM(order_items[line_total])Orders = COUNTROWS(orders)
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 | Orders | Average Order Value |
|---|---|---|---|
| app | 995,524.18 | 1,904 | |
| marketplace | 324,298.57 | 630 | |
| web | 980,346.78 | 1,761 | |
| Total | 2,300,169.53 | 4,295 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Robert gets, and why
The lesson
This boss level is three measures, built the way you’d build them in Power BI Desktop: one at a time, each tested in the visual before you write the next.
The report you’re working in
The practice report has a matrix with orders[channel] on its rows (web, app, marketplace) and a
Total row. The page already has two filters, the kind you set in the Filters pane under
Filters on this page: 'Date'[Year] is 2025 and orders[status] is completed. They apply to
every measure on the page, so your formulas don’t need to mention them.
The DAX engine here is built for this site and follows Power BI’s rules, so the measures work unchanged in Desktop. There: select a table in the Data pane, Home → New measure, type the formula, press Enter, then drag the measure into the matrix’s Values. Repeat for each one.
Step 1: Sales
Sales = SUM(order_items[line_total])line_total is each order line’s value after its discount. Each cell sums the lines it can see:
the page filters and the row’s channel travel from orders to order_items along the
relationship, so the web row adds up only web lines from completed 2025 orders.
Step 2: Orders
Orders = COUNTROWS(orders)COUNTROWS counts the rows of a table in the cell’s filter context. orders holds one row per
order, so this is the number of orders. Counting order_items would count order lines, which is
a different number (about 2.5 lines per order).
Step 3: Average Order Value
Average Order Value = DIVIDE([Sales], [Orders])A measure can use other measures: write their names in square brackets. DIVIDE(a, b) returns
a / b, or blank when b is zero or blank, where a / b would show an error or infinity. Use it
for every ratio.
Why the Total isn’t an average of the rows
On each channel row, Average Order Value is that channel’s sales over that channel’s orders. On the Total row Power BI evaluates the measure once more with no channel filter: all sales over all orders. The app and the web each have about three times as many orders as the marketplace, so they weigh more. That’s the right number for “what is an average order worth?” ($535.55); averaging the three row values would give each channel equal weight and a wrong answer.
Format it
Average order value is money: select the measure, then Measure tools → Format → Currency and two decimals. The practice matrix does that for you in step 3.
What it tells Robert
In 2025 the app sold the most ($995.5K from 1,904 orders), just ahead of the web ($980.3K from 1,761), with the marketplace at $324.3K from 630. Yet the web’s average order is the biggest, about $557 against $523 in the app and $515 on the marketplace: the app wins on the number of orders, not their size. A channel can sell less because it has fewer orders or because each order is smaller, and the two problems have different fixes, which is why Robert asked for all three.
Lantern lit!
Practised: daxmeasuresumcountrowsdivideaverage order valuefilters panefilter context
- Solved
- No hints
- First run