The request
From Monica Reyes, Merchandise Planner
“I need gross margin % by category for the range review, weighted by what we actually sold. Careful: nine products still have no unit cost in the catalogue. If they count as free, Furniture looks like a 70% margin business, which it isn't.”
Your goal
The matrix has products[category] on its rows; a slicer keeps completed orders. Create a measure called Margin %: (revenue − cost) / revenue, where revenue is order_items[line_total] and cost is order_items[quantity] × the product's unit_cost. Leave out every order line whose product has a blank unit_cost, from both revenue and cost. Helper measures are fine; the last one is checked.
- Fill
[Margin %]on every row of the visualnot done yet - The Total row right toonot done yet
Your measure
- Visual
- A matrix: rows = products[category]; values = [Margin %]
- Slicers
- 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
| category | Margin % |
|---|---|
| Decor | |
| Furniture | |
| Kitchen | |
| Lighting | |
| Outdoor | |
| Textiles | |
| Total |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Monica gets, and why
The lesson
A margin % is a ratio of two sums, and both sums have to come from the same rows. Monica’s catalogue has a hole in it, so the real work in this ticket is deciding what to do with the rows that fall through the hole.
What you are working in
The matrix below is a practice report built for this site; Power BI Desktop runs only on Windows. A DAX engine evaluates your measures in every cell following Power BI’s rules. To do it for real: Home → New measure for each of the measures, then Measure tools → Format → Percentage on Margin % so 0.534 shows as 53.4%.
RELATED: reach across a relationship, one row at a time
Cost per line is quantity × unit cost, but the two numbers live in different tables. Inside an
iterator over order_items, RELATED(products[unit_cost]) follows the relationship from the
current line to its one product and returns that product’s cost:
Cost =
SUMX(
order_items,
order_items[quantity] * RELATED(products[unit_cost])
)RELATED works from the many side to the one side (line → product). The other direction,
from a product to its many lines, is RELATEDTABLE.
Weighted, not averaged
DIVIDE(revenue − cost, revenue) over the cell’s lines is a weighted margin: a product
that sold $200,000 moves it far more than one that sold $2,000. The alternative,
AVERAGEX(order_items, <line margin %>), gives every line one vote, so a cheap accessory counts
as much as a sofa. Managers mean the weighted number when they say “our margin”.
Deciding what blank costs mean
Nine products have no unit_cost. In arithmetic, DAX treats blank as zero, so the lines of
those products add revenue but no cost: 100% margin. That is how Furniture reaches 70%.
You have three honest choices: get the costs, estimate them and say so, or leave those lines out of both sums. The last is what Monica asked for:
Costed Revenue =
SUMX(
FILTER(order_items, NOT ISBLANK(RELATED(products[unit_cost]))),
order_items[line_total]
)Use the same FILTER in the Cost measure, then:
Margin % = DIVIDE([Costed Revenue] - [Cost], [Costed Revenue])Leaving them out of only the cost side is the mistake to avoid: then the missing products still count in revenue and inflate the margin.
Measures that call measures
Splitting the logic into Costed Revenue, Cost and Margin % keeps each piece short and testable: drop Cost into the matrix on its own and check it before you divide. In this editor, write all three; the last one is the one checked.
Common mistakes
- Treating blank as zero cost. Decide what blanks mean before you divide.
- Averaging percentages. Weight by sales: ratio of sums, not average of ratios.
- RELATED outside an iterator. It needs a current row; in a plain measure there is none.
Lantern lit!
Practised: sumxrelatedweighted averagemarginblanksrow context
- Solved
- No hints
- First run