Lesson 1: Measures vs Calculated Columns

Welcome, detective

You've built a report. You've written a few measures. Then one day a total looks wrong, or a number moves when someone clicks a slicer, and you can't say why. This course teaches you to find out why. Each level gives you a small model, a calculation that misbehaves and a way to prove the fix.

Note: This course is independent of Microsoft. It is not affiliated with, endorsed by or sponsored by Microsoft. We use Microsoft's standard terms so that what you learn here matches the official documentation.

We start with the most basic decision in DAX: should this calculation be a calculated column or a measure?

The two kinds of calculation

Think of a spreadsheet with a printed report pinned beside it:

• A calculated column is like a formula filled down a new column. Power BI works it out row by row when the data is refreshed and stores every result in the model. In an Import model the values are materialized: they take memory and add to refresh time.
• A measure is like a formula on a calculator that runs only when someone asks a question. The model keeps the DAX expression, not the results. The measure is worked out at query time, each time a visual asks for it, using whatever filters the visual, slicers and filter pane apply at that moment.

That difference explains nearly everything else in this lesson.

The case file: a tiny Sales table

Load this table into Power BI Desktop (Enter data is fine):

Walkthrough 1: a calculated column

Right-click the Sales table and choose New column, then type:

// Calculated column: evaluated once per row, at refresh, and stored
Line Amount = Sales[Quantity] * Sales[UnitPrice]
// Row 1 -> 10 * 2.00 = 20
// Row 5 -> 1 * 40.00 = 40

Open Table view and you'll see a value on every row: 20, 10, 20, 10, 40, 80. Those six numbers are now stored in the model. A Line Amount column is a reasonable choice here because it's a row-level fact.

Walkthrough 2: measures

Select Modeling > New measure and create:

// Measure: only the expression is stored; the visual's filters decide the result
Total Sales = SUM ( Sales[Line Amount] )

Total Quantity = SUM ( Sales[Quantity] )

Add a table visual with Product, Total Sales and Total Quantity:

You wrote one formula and got four different answers. Each row of the visual asks the measure the question again under a different filter: "only Bag", "only Notebook", and so on. Later levels explain exactly where those filters come from. For now, remember that a measure responds to filters and a stored column does not.

The misbehaving number: an implicit measure

A colleague drags UnitPrice straight into the same table visual. Power BI quietly creates an implicit measure, which defaults to Sum for numeric fields. You can spot one by its name in the field well: Sum of UnitPrice. In the Data pane, the numeric column has a Σ beside it.

A bag costs 40, not 80. Adding prices together means nothing. Switching the visual to Average gives 40 per product, but the total row becomes the plain average of six rows (15.67). The average price per unit sold is actually 180 / 24 = 7.50.

The fix: write a named explicit measure that holds the business logic.

// Explicit measure: named, reusable, and the logic is visible in DAX
Avg Price per Unit =
DIVIDE ( [Total Sales], [Total Quantity] )
// DIVIDE is a safe division (more on it in a later level)
// Bag 120/3 = 40 | Notebook 30/6 = 5 | Pen 30/15 = 2 | Total 180/24 = 7.50

Remove Sum of UnitPrice from the visual and add Avg Price per Unit instead. To prove the fix, check one row by hand: Pen sold 15 units for 30, which is 2.00 each. ✔

Explicit measures beat implicit ones for three reasons:

• One definition. Every visual uses the same logic. Nobody has to remember to switch the aggregation from Sum to Average.
• A meaningful name. "Avg Price per Unit" tells the reader what it is. "Sum of UnitPrice" just repeats a mistake.
• Reuse. Measures can call other measures, as [Total Sales] does above.

Best practice: In Model view, open Model explorer, select the semantic model and turn on Discourage implicit measures. The Σ disappears, and you can no longer drop a raw column into a visual's values. Implicit measures that already exist in visuals keep working, so you still need to find and replace them yourself.

The second trap: a ratio stored in a column

Suppose someone builds "share of sales" as a calculated column:

// Calculated column: SUM here sees the whole table at refresh = 180
Share Column = DIVIDE ( Sales[Line Amount], SUM ( Sales[Line Amount] ) )
// Row 1 -> 20 / 180 = 0.111

Set a slicer to Pen and the visible rows still show 0.111 and 0.056. The two Pen rows make up the whole selection, yet together they show about 17%. The value was fixed at refresh and can't see the slicer. Summing the column also gives you a percentage total that is just a sum of stored numbers. A ratio that should react to the user's selection belongs in a measure. Percent of total gets a full lesson later.

Choosing: a quick decision guide

A working rule from production: default to a measure. Use a column only when you need to slice, filter or group by the value, or when it is a genuine attribute of a single row.

Key takeaways

• Calculated columns are evaluated row by row at refresh and stored. Measures store only their DAX and are evaluated when a visual asks.
• Measures respond to slicers and filters. A stored column's values don't change when you click.
• "Sum of …" in a field well means an implicit measure. Replace it with a named explicit measure.
• Discourage implicit measures blocks new implicit measures but leaves existing ones in place.
• Use a column when you need to slice or group by a value. Use a measure for aggregations and ratios.
• Always check one row by hand before you trust the number.