Assignment A05 — The Measure Family
Skills assessed. Calculated columns · explicit measures · SUMX and RELATED ·
measures composed from measures · DIVIDE · DISTINCTCOUNT · formatting.
Submit: A05_LastFirst.pbix. Worth 20 points. Start from your completed A04
star. The grader checks measures by exact name and by their unfiltered values
— the named contract at its purest.
Scenario
The VP saw the star and asked the obvious next question: "where are the numbers?" Finance wants a defined, documented measure family that every future report page draws from — one definition per concept, named so a reader can tell what each number is.
Requirements
- One calculated column on sales:
Line Revenue— quantity × unit price × (1 − discount). - Eight measures, with these exact names and these verified unfiltered
values:
Measure Definition Unfiltered value Total RevenueSUMX over sales (or SUM of Line Revenue) $66,813,563.97 Total CostSUMX with RELATED unit cost $28,099,192.18 Gross Profit[Total Revenue] − [Total Cost] $38,714,371.79 Gross Margin %DIVIDE(profit, revenue), % format 57.94% Total UnitsSUM of quantity 259,421 Order CountDISTINCTCOUNT of OrderID 85,538 Average Order ValueDIVIDE([Total Revenue], [Order Count]) $781.10 Return RateDIVIDE(returned units, units sold), % format 3.21% - Composition required: Gross Profit and Average Order Value must reference other measures by name, not repeat their formulas. (The grader reads the expressions.)
- Formats: currency measures as $ with two decimals; percents as %; counts as whole numbers with thousands separators.
- One report page "Measures" with four cards — Total Revenue, Gross Margin %, Average Order Value, Return Rate — and a Category slicer. Click a category: all four must recompute (Return Rate proves your star still crosses facts).
DIVIDE, not the slash
Both division measures must use DIVIDE. The grader reads the expression — a slash is a lost
point even when today's value matches, because the contract is the habit, not just the number.
Checklist (what the grader checks)
| # | Check | Points |
|---|---|---|
| 1 | Line Revenue column present, per-row values correct (spot totals) | 2 |
| 2 | Eight measures exist with the exact names | 4 |
| 3 | All eight unfiltered values match (tolerance one cent / 0.01pp) | 6 |
| 4 | Gross Profit and AOV reference measures by name; DIVIDE used in both ratio measures | 3 |
| 5 | Formats set on the measures (not per-visual) | 2 |
| 6 | "Measures" page: four cards + Category slicer, all recomputing | 2 |
| 7 | File named A05_LastFirst.pbix | 1 |
Before you submit
- Read each card against the table above with the slicer cleared. One mismatch means one definition drifted — the values are the audit.
- Click Lighting: Return Rate should move to its category figure, not sit at 3.21%. A frozen rate means a broken relationship path, not a broken measure.
- Save, close, reopen, verify.