Chapter 5 · Measures

Measure Architecture

A P&L model quickly collects dozens of measures, and a real one hundreds: every line, as actual, last year and period to date, with variances and ratios. Without structure, the same logic gets copied into many measures and they slowly disagree. This chapter shows the structure of the AdventureWorksPL measures: one home, four layers, and naming that mirrors the finance report.

One home for every measure

Measures can live on any table, and in inherited models they are usually scattered: some on the fact, some on dimensions, some on hidden helper tables. Users can't find them and authors don't know where to add new ones. Create one table whose only job is to hold measures:

_Measures = ROW ( "Placeholder", BLANK () )

Hide the Placeholder column. Power BI then shows _Measures with a calculator icon at the top of the field list. All 36 measures in the model live there; a measure's DAX doesn't depend on its home table, so moving measures into it never changes a number.

Four layers

1 · Base: one column, hidden→ 2 · Line: P&L line with shared logic→ 3 · Derived: arithmetic on lines→ 4 · Analysis: ratios, LY, variance

Layer	Reads	Contains	Visible
1 · Base	Exactly one column	`SUM`, nothing else	No
2 · Line	Base measures and helpers	The shared rules: currency conversion	Yes
3 · Derived	Line measures only	+ and −: Net Sales = Gross Sales − Discounts	Yes
4 · Analysis	Line and derived measures	Ratios with `DIVIDE`, LY, variances	Yes

The rule that makes it work: a measure only reads the layer below it. Only base measures (and the few helpers that apply a rule) touch columns. Shared logic is written once, in layer 2, and everything above inherits it. If a P&L line is wrong, there is exactly one place to look.

Layer 1: base

_Gross Sales Base  = SUM ( Sales[GrossSales] )
_Discounts Base    = SUM ( Sales[Discounts] )
_Product Cost Base = SUM ( Sales[ProductCost] )
_Units             = SUM ( Sales[Units] )

Layer 2: line

A line measure applies the shared rules. For lines 01, 02 and 04 the rule is currency conversion, which Chapter 6 builds as _Local Gross Sales and its siblings; for now, read them as "the base measure, converted when the user picks one other currency".

01 - Gross Sales    = [_Local Gross Sales]
02 - Discounts      = [_Local Discounts]
04 - Product Cost   = [_Local Product Cost]

Layer 3: derived

03 - Net Sales    = [01 - Gross Sales] - [02 - Discounts]
05 - Gross Profit = [03 - Net Sales] - [04 - Product Cost]

Never re-derive a line from columns

If 03 - Net Sales were written as SUM(Sales[GrossSales]) - SUM(Sales[Discounts]), it would skip the currency logic in layer 2. It would agree with Gross Sales − Discounts in USD and disagree as soon as someone picks AUD. A derived measure that goes back to columns is easy to spot in the documentation generated in Chapter 8: it lists source columns where it should only list other measures.

Layer 4: analysis

Gross Margin %     = DIVIDE ( [05 - Gross Profit], [03 - Net Sales] )
Net Sales per Unit = DIVIDE ( [03 - Net Sales], [_Units] )

03 - Net Sales vs LY   = [03 - Net Sales] - [03 - Net Sales LY]
03 - Net Sales vs LY % = DIVIDE ( [03 - Net Sales vs LY], [03 - Net Sales LY] )

Ratios are always measures, computed from totals at the level being viewed. A stored per-row margin percentage averaged in a visual is wrong at every level above the row: Adventure Works' FY2013 gross margin is 9.5%, while the average of its order lines' margins is something else entirely. DIVIDE returns blank instead of an error when the denominator is zero.

Display folders that read like the report

The line numbers already sort the field list in P&L order. Display folders group the variants:

_Measures
├── P&L Actual        01 - Gross Sales … 05 - Gross Profit
├── P&L Last Year     03 - Net Sales LY, 05 - Gross Profit LY
├── Variance          03 - Net Sales vs LY, 03 - Net Sales vs LY %
├── Ratios            Gross Margin %, Net Sales per Unit
├── Period To Date    03 - Net Sales PTD
└── zz Helpers        _Gross Sales Base, _Units, _Local …  (hidden)

Number the visible measures, never the helpers. The line number is part of the business vocabulary ("line 05 is down"); helpers are implementation.

Format strings and descriptions

Every visible measure gets a format string. Amounts as #,0, percentages as 0.0%, per-unit values as #,0.00. The model doesn't put a $ in amount formats, because the same measure can show AUD or GBP (Chapter 6). Without format strings, the same number shows with different decimals on different pages.
Every visible measure gets a description. One sentence in business language: "Gross sales less discounts, in USD unless one other currency is selected." It appears as a tooltip in the field list and in Excel. In TMDL it is the /// line above the measure.
Calculation-group items (Chapter 4) use a format-string expression so "vs LY %" shows as a percentage whatever measure it is applied to.

	/// Gross sales less discounts, in USD unless one other currency is selected.
	measure '03 - Net Sales' = [01 - Gross Sales] - [02 - Discounts]
		formatString: #,0
		displayFolder: P&L Actual

Anti-patterns to avoid

Pattern	Problem	Instead
Same column summed in several measures, each with its own filters	Logic drifts; one copy gets fixed, the others don't	One base measure; filters applied once in layer 2
`FILTER ( Sales, … )` inside `CALCULATE`	Iterates the whole fact table	Filter a dimension column: `CALCULATE ( [m], Channel[Channel] = "Reseller" )`
`IF ( [b] = 0, 0, [a] / [b] )`	Verbose, evaluates `[b]` twice	`DIVIDE ( [a], [b] )`
Hard-coded IDs: `Sales[CurrencyKey] <> 100`	Meaning hidden in a number; breaks if keys change	A flag column from SQL: `Currency[IsReportingCurrency]`
A visible measure for every column "just in case"	A field list nobody can navigate	Measures for P&L lines and agreed KPIs only
Long measures with repeated sub-expressions	Hard to read; recomputed each time	`VAR` … `RETURN`, or a helper measure

Exercise: a new line

Finance wants freight charged to customers shown as a new line, "06 - Freight Income", after Gross Profit, with a subtotal that includes it. The warehouse has it in the Freight column of both fact tables, in USD like other amounts. Which queries and measures do you add or change?

Answer

Add CAST(s.Freight AS decimal(19, 4)) AS Freight to the Sales source query (and Freight to both halves of the union), then a hidden column Sales[Freight]. Add a base measure _Freight Base, a currency helper _Local Freight like the other three, and a visible line measure 06 - Freight Income = [_Local Freight]. Then one derived measure for the subtotal: 07 - Gross Profit incl. Freight = [05 - Gross Profit] + [06 - Freight Income]. The calculation group gives both LY and variances for free.

Key takeaways

One disconnected measure table holds every measure.
Four layers: base reads columns, line applies shared rules, derived does arithmetic, analysis adds ratios and comparisons.
A measure only reads the layer below it, so each rule exists exactly once.
Number P&L lines, group variants in display folders, and give every visible measure a format string and a description.