Calculated Column vs Measure in Power BI: The Rule That Decides

1. The fork, and the one question that decides it

Here is the fork laid out fully, because the choice is not obvious the first time. The question is: where should this calculation live? There are two possible answers. A calculated column is a new column added to a table. Power BI computes its value once per row, at the moment the data loads, and then stores every one of those values in the model. The values never change again until the next refresh. A measure is a stored formula with no stored values at all. Power BI computes it fresh at query time, which means the instant a visual needs it, and it computes it inside whatever filters that visual has right then: the slicer selections, the page filters, the row of the chart it is filling in.

What decides between them is one question. Does the answer depend on what the viewer has filtered? If the answer changes when someone clicks a slicer, it must be a measure, because a column's values were frozen before the viewer ever arrived. If the answer is a fixed fact about one row, a fact that stays true no matter who is filtering what, a column is allowed and sometimes right.

Why the question matters: getting it wrong does not produce an error. It produces a clean, formatted, plausible number that is wrong, and nothing on the screen warns anyone. The rest of this page is one worked example of exactly that.

2. Row context and filter context in everyday words

Before the explanation: a slicer click changes a card from 21.4% to 16.7%. One of the two tools could have reacted to that click and one could not. Which is which?

These two phrases sound like jargon and they are actually just the two moments from section one, named. Row context means the formula is standing on one row and can see that row's values. A calculated column has row context: while computing row 1 it sees row 1's Sales and row 1's Profit, and nothing else. Filter context means the formula is standing inside a set of filters and can see every row that survives them. A measure has filter context: when a card is filtered to East, the measure sees all the East rows at once and none of the others.

Here is the table the rest of the page uses. Four rows, two products, two regions.

A calculated column walks this table one row at a time, at load, and writes one stored value per row. A measure never walks rows on its own. It waits for a visual to hand it a filtered slice, East only, or product B only, or everything, and then computes over that slice. Same table, two completely different moments of arithmetic. That is the entire distinction, and the answer to the prequestion is that only the measure could have moved when the slicer did.

3. The classic failure: a margin column, summed

Margin percent is profit divided by sales. It looks like a fact about each row, so the natural first instinct is a calculated column.

Margin = Sales[Profit] / Sales[Sales]   -- calculated column, one value per row

At load, Power BI computes and stores four values, and each one is genuinely correct for its row.

Now drag that Margin column into a card. Power BI has to collapse four stored values into one, and its default for a numeric column is to sum them: 50% + 45% + 10% + 15% = 120%. The card says total margin is 120%, which is not just wrong but impossible. Switch the aggregation to average instead and you get (50% + 45% + 10% + 15%) ÷ 4 = 30%, which looks respectable and is still wrong, because it lets product A's tiny 100 in sales count exactly as much as product B's 500.

The true overall margin is total profit over total sales: profit is 50 + 90 + 50 + 45 = 235, sales is 100 + 200 + 500 + 300 = 1,100, and 235 ÷ 1,100 = 21.4%. Neither 120% nor 30% is anywhere near it.

Say why the 120% appeared, in your own words, before reading on. The reason is that percentages of different-sized bases cannot be added. Adding 50-out-of-100 to 10-out-of-500 as if both were out of the same base makes the small product speak five times louder than its sales justify. The column froze each ratio at row level, so every aggregation of it afterwards is an aggregation of ratios, and ratios do not aggregate.

4. The measure that gets it right, checked by hand

The fix is to keep the division waiting until after the filters have done their work. That is a measure.

Margin % = DIVIDE ( SUM ( Sales[Profit] ), SUM ( Sales[Sales] ) )

DIVIDE is division that returns a blank instead of an error when the bottom is zero, which matters the day a filter combination leaves no sales rows. The shape of the formula is the whole lesson: sum first, divide last.

Now every filter the viewer applies produces the honest number, because the sums happen inside the filter context. Check each one by hand:

One formula, four filters, four correct answers, and the measure was never told which filter was coming. That is what "computed at query time inside the filter context" buys you. Notice too that the column's wrong answers are not even consistently wrong: East and West both say 60% while the truth is 16.7% against 27.0%, so the column version erases a real regional difference entirely.

Picture running this on your own model for a moment. Take one ratio you report, margin, conversion rate, cost per unit, and imagine a slicer cutting it to one region. If that number is a calculated column today, the sliced version on your screen is some flavour of the 60% row above.

5. What columns cost in storage

Correctness is the main reason to prefer measures, but there is a second, quieter cost. A calculated column stores one value for every row of its table. On the four-row table here that is four numbers and nobody cares. On a ten-million-row fact table, one calculated column is ten million stored values, held in memory, refreshed on every load, and compressed worse than the columns that came from the source, because calculated columns are computed after the engine has already chosen its compression. A measure stores exactly one thing regardless of table size: the formula text. Ten characters or ten million rows of difference, per calculation, is a real gap once a model has twenty of them.

6. When a column is the right answer

Before the explanation: you want a slicer with the options Budget, Mid-range, and Premium. Could a measure power that slicer?

It could not, and that is the clean rule for when a column is right. A slicer, an axis, and a legend can only hold real columns. They need a stored value on every row to group by. So when you want to slice or group by a category that does not exist in the source data, you build it, and a calculated column is the honest home for it. A price band is the classic case: it is a fixed fact about one row, it does not change when the viewer filters, and its whole job is to be filtered by.

Price Band =
SWITCH ( TRUE (),
    Sales[Sales] < 150, "Budget",
    Sales[Sales] < 350, "Mid-range",
    "Premium"
)

One paragraph on SWITCH(TRUE()), because it looks odd the first time. SWITCH normally matches one value against a list of exact cases. Handing it TRUE() turns it into a ladder of conditions checked top to bottom, and the first condition that comes up true wins. It is DAX's version of a tidy IF-ELSE chain, and it is the standard way to write banding columns without nesting five IFs inside each other. On our table it stores Budget for the 100 row, Mid-range for the 200 and 300 rows, and Premium for the 500 row, and those labels pass the deciding question: no slicer click can change which band a 500-dollar row belongs in.

The band thresholds themselves, 150 and 350 here, deserve a sentence of justification in your model notes rather than a shrug. Choosing thresholds from the data is its own guide.

7. Implicit and explicit measures

Drag a bare numeric column into a visual and Power BI quietly wraps it in an aggregation for you, usually SUM. That auto-created wrapper is called an implicit measure. An explicit measure is one you wrote yourself with a name and a formula, like the Margin % above.

Analysts write explicit measures, for three reasons that all showed up earlier on this page. First, the implicit default is exactly the machine that turned the margin column into 120%: it sums whatever it is handed, whether or not summing means anything. Second, an explicit measure carries its definition with it, so Margin % means profit over sales in every visual, every page, and every colleague's report, instead of whatever aggregation someone happened to leave selected. A shared, written definition is most of what makes a metric trustworthy, and defining metrics goes deeper on that. Third, only explicit measures can hold real logic: DIVIDE, filters, time comparisons. The habit is simple and cheap: even for a plain total, write Total Sales = SUM ( Sales[Sales] ) once and reuse it everywhere.

Edge cases that produce confident wrong numbers

Before the explanation: a colleague averages the margin column instead of summing it and says the problem is fixed. Is it?

Averaging the ratio column is the same mistake in a milder coat. This is the prequestion's answer. (50% + 45% + 10% + 15%) ÷ 4 = 30%, against a truth of 21.4%. An unweighted average lets a row with 100 in sales count as much as a row with 500. It is closer than 120%, which is exactly why it survives review.

A ratio at a subtotal is not the sum of the ratios below it. Even with a correct measure, people expect the total row of a matrix to be the sum of the rows above. For a ratio it never is: East 16.7% and West 27.0% sit above a total of 21.4%, not 43.7%. That is correct behaviour, and it is worth one sentence in the report notes so nobody "fixes" it.

A ratio built on unrelated totals ignores the visual. Wrap the sums in functions that remove filters, or build them on the wrong table, and the measure shows 21.4% in every row of a regional breakdown. The symptom is a measure repeating one value everywhere; the cause is the filter context being thrown away.

A calculated column does not update until refresh. Columns are computed at load, so a banding column built on yesterday's prices stays stale until the next refresh, while a measure would have moved with the data underneath. Anything that must track the data live has to be a measure.

Blank divisions. Filter to a combination with rows but zero sales and a raw / errors or shows infinity. DIVIDE returns blank, and a blank card is the honest display for "no base to divide by".