Measures on measures
The model now has [Sales Amount] and [Total Cost]. A measure can use other measures: [Sales Amount] - [Total Cost] is the margin, computed in each cell's filter context.
For ratios, use DIVIDE(numerator, denominator). It returns BLANK instead of an error when the denominator is zero or blank.
A ratio of totals is not the same as an average of ratios. Divide the margin of the whole cell by the sales of the whole cell.
Your task
Write a measure named Margin %: sales amount minus total cost, divided by sales amount.
About this exercise
You practise reusing measures and divide with DIVIDE. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Margin %, fills a Power BI-style matrix grouped by Product[Category] and Date[Year]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount and Total Cost. Reference them in square brackets, for example [Sales Amount].
Last updated 10 November 2026
Solution, expected result and common mistakes
Worked solution
Margin % = DIVIDE([Sales Amount] - [Total Cost], [Sales Amount])Measures compose: each one is evaluated in the same cell's filter context, so ratios stay correct at every level, totals included.
The expected result
| Year | |||
|---|---|---|---|
| Category | 2024 | 2025 | Total |
| Accessories | 64.4% | 65.0% | 64.7% |
| Bikes | 46.7% | 46.1% | 46.4% |
| Clothing | 62.5% | 63.1% | 62.8% |
| Total | 50.1% | 49.3% | 49.7% |
Common mistakes
Margin % = DIVIDE([Total Cost], [Sales Amount])That is the cost ratio. The margin is what is left after cost: [Sales Amount] - [Total Cost].
Margin % = [Sales Amount] - [Total Cost]That is the margin in money. The task asks for a percentage: divide it by [Sales Amount].
Margin % = AVERAGEX(Sales, DIVIDE(Sales[Unit Price] - RELATED(Product[Standard Cost]), Sales[Unit Price]))That averages the margin % of each line, so a water bottle counts as much as a road bike. Divide the cell's total margin by its total sales instead.
Margin % = DIVIDE([Sales Amount] - [Total Cost], [Total Cost])That is the markup on cost. Margin % divides by sales, not by cost.
Hints
- Use the two measures in the model: [Sales Amount] and [Total Cost].
- DIVIDE( [Sales Amount] - [Total Cost], ... )
Opening the exercise…