Measures vs calculated columns

1. Calculated column — one value per row

Written in the Power Pivot grid (click the "Add Column" header). Calculated once per row on refresh and stored in the model, like a helper column:

Line Revenue = fact_Sales[Qty] * fact_Sales[UnitPrice] * (1 - fact_Sales[Discount])
Line Cost    = fact_Sales[Qty] * RELATED(dim_Products[UnitCost])

RELATED brings a value from the "one" side of a relationship into the row — the DAX version of XLOOKUP.

Columns can go in Rows, Columns, slicers and filters (e.g. a "Size Band" column).

2. Measure — calculated in the pivot cell

Written in the calculation area below the grid (or Power Pivot tab → Measures → New Measure). Not stored per row — calculated for each pivot cell based on the filters that apply to that cell (region, category, month…):

Total Revenue := SUM(fact_Sales[Line Revenue])
Total Cost    := SUM(fact_Sales[Line Cost])
Profit        := [Total Revenue] - [Total Cost]
Margin %      := DIVIDE([Profit], [Total Revenue])
Orders        := COUNTROWS(fact_Sales)
Avg Order Value := DIVIDE([Total Revenue], [Orders])

:= is how Power Pivot shows measures; in the New Measure dialog you type only the part after it.

For FY2025-26 in the capstone: Total Revenue 29,49,96,410, Total Cost 24,61,94,250, Profit 4,88,02,160, Margin % 16.5%, Orders 26,309.

3. Why Margin % must be a measure

As a calculated column, Profit / Revenue per row, then summed in a pivot, gives the sum of percentages — nonsense (thousands of %). A measure divides total profit by total revenue for whatever the cell represents — North, Electronics, October — always correct. Same lesson as calculated fields in Module 2, but now done properly.

4. Choosing

Rule of thumb: if it's a number you'll aggregate, make it a measure. Use columns for categories and row-level inputs that measures need.

(Line Revenue as a column is fine; even better is a SUMX measure that skips the column — Lesson 4.)

5. Good measure habits

• Put all measures in one table (e.g. the fact table, or an empty "_Measures" table) so they're easy to find.
• Set the format in the measure dialog (Currency ₹, Percentage) — it follows into every pivot.
• Build measures on measures (Profit uses [Total Revenue]): fix once, correct everywhere.
• Always use DIVIDE(a, b) instead of a / b — it returns blank instead of an error when b is 0.
• Reference columns with table names (fact_Sales[Qty]) and measures without ([Profit]).

6. Implicit vs explicit measures

Dragging a numeric field to Values creates an implicit "Sum of Line Revenue". It works, but can't be reused in other measures or CUBE formulas. Write explicit measures for anything you report.

Common mistakes

Calculated column for a ratio. Using / and getting errors on zero rows. Measures scattered across tables with default formats.