The request
From Victoria Grant, Chief Commercial Officer
“Holiday quarter post-mortem on Monday. Per sales channel I need three things: how much we gave away in discounts, where each channel ranks on sales, and, for web and app only, how far each sits above or below the average of the two.”
Your goal
Step 1 of 3. Create a measure called Discount Given: the money taken off list price on every order line, order_items[quantity] × order_items[unit_price] × order_items[discount_pct] / 100, added up.
- Fill
[Discount Given]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Add a measure called Channel Rank: each channel's rank by [Sales] among all channels, 1 for the biggest. The Total row is hidden.
- Fill
[Channel Rank]on every row of the visualnot done yet
Step 3 of 3. A channel slicer now keeps web and app. Add a measure called vs Selected Avg: ([Sales] − the average [Sales] of the selected channels) / that average. Store the row's sales and the average in VARs; the average is AVERAGEX over the selected channels. The Total row is hidden.
- Fill
[vs Selected Avg]on every row of the visualnot done yet
Step 1 · your measure
- Visual
- A matrix: rows = orders[channel]; values = [Sales], [Discount Given]
- Slicers
- orders[status] = completed'Date'[Year Quarter] = 2025 Q4
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), 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 | Discount Given |
|---|---|---|
| app | $329,316 | |
| marketplace | $121,886 | |
| web | $304,108 | |
| Total | $755,310 |
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 = [Sales], [Channel Rank]
- Slicers
- orders[status] = completed'Date'[Year Quarter] = 2025 Q4
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), 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 | Channel Rank |
|---|---|---|
| app | $329,316 | |
| marketplace | $121,886 | |
| web | $304,108 |
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], [vs Selected Avg]
- Slicers
- orders[status] = completed'Date'[Year Quarter] = 2025 Q4orders[channel] = web, app
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), 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 | vs Selected Avg |
|---|---|---|
| app | $329,316 | |
| web | $304,108 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Victoria gets, and why
The lesson
Victoria’s three questions use three different patterns from this chapter, all in one visual: a matrix of the three sales channels for the holiday quarter, completed orders only.
What you are working in
As in every level of this track, this is a practice report built for this site: Power BI Desktop runs only on Windows, and a DAX engine on the page fills each cell following Power BI’s rules. Each step checks the last measure in the editor; keep the earlier ones there. In Power BI Desktop you would add each one with Home → New measure and drop it into the matrix’s Values.
Step 1: discounts, line by line
A discount is worked out per line from three of its columns: quantity × unit price ×
discount_pct / 100. That’s an iterator over order_items, exactly like Net Revenue earlier in the
chapter, with the discount share instead of the share the customer pays. The channel row and the
two slicers decide which lines SUMX walks.
Step 2: a rank that ignores the row
RANKX(ALL(orders[channel]), [Sales]) evaluates [Sales] for each of the three channels and
returns the current row’s position. ALL is what lets all three compete; without it each row
competes only with itself. The Total row is hidden because a rank has no meaning there.
Step 3: against the average of what’s selected
Marketplace is reviewed separately, so the page now has a channel slicer on web and app. The measure compares each row with the average of the selected channels:
vs Selected Avg =
VAR ThisSales = [Sales]
VAR AvgSales = AVERAGEX(ALLSELECTED(orders[channel]), [Sales])
RETURN
DIVIDE(ThisSales - AvgSales, AvgSales)ALLSELECTED(orders[channel])removes the row’s channel filter but keeps the slicer’s, so the list is web and app.- AVERAGEX evaluates
[Sales]for each and averages them: one number per channel, then the mean. - The two VARs name the pieces, so RETURN reads like the definition in the ticket.
A positive result means the channel sells more than the average of the selected channels; a negative one, less. With two channels the results are mirror images: one is as far above the average as the other is below.
What to check before Monday
Read the matrix, not just the green tick: does Discount Given look plausible next to Sales (a few percent, not half)? Does the rank match the order of the Sales column? Do the step 3 results sum to zero across the selected channels? Sanity checks like these catch the bugs a checker can’t.
Lantern lit!
Practised: sumxrankxallselectedaveragexvarboss
- Solved
- No hints
- First run