Measures vs Calculated Columns in Power BI

The same formula, two very different things

You need profit margin. Both of these look reasonable:

-- as a calculated column
Margin = ( Sales[Amount] - Sales[Cost] ) / Sales[Amount]
 
-- as a measure
Margin % = DIVIDE( SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]) )

They are not two styles of the same thing. They are computed at different times, stored differently, and answer different questions. Choose wrong and you get a model that's either enormous or quietly incorrect — and the incorrect case is the one that reaches a slide deck.

When each one runs

A calculated column is evaluated once, at refresh, for every row in the table. The results are stored in the model exactly like a column loaded from the source. By the time anyone opens the report, the values are fixed.

A measure stores nothing. It's a formula evaluated on demand, once per cell of whatever visual is asking, using only the rows that survive that cell's filters. Change a slicer and every measure recalculates.

That single difference produces everything below.

Row context and why averaging margins is wrong

A calculated column has row context: while it evaluates, "the current row" exists, so Sales[Amount] means this row's amount. A measure has no row context; it has filter context, so Sales[Amount] alone is meaningless and you must aggregate — hence SUM.

This is where the classic error lives. With Margin as a calculated column, the obvious way to show it on a report is AVERAGE(Sales[Margin]). That gives you the average of the per-row percentages, weighting a 10-taka sale identically to a 10-million-taka sale. It is not your margin.

-- wrong: mean of ratios
Avg Margin = AVERAGE( Sales[Margin] )
 
-- right: ratio of totals
Margin % = DIVIDE( SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]) )

Ratios must be recomputed at every level of aggregation, never averaged up from row level. Any percentage, rate, or per-unit figure belongs in a measure for this reason alone. A row-level ratio stored in a column is a trap waiting for someone to drag it into a visual.

Use DIVIDE rather than / while you're at it — it returns blank instead of an error when the denominator is zero, which is what you want in a visual.

The memory cost nobody budgets for

Calculated columns are stored, so they're compressed like any other column — and compression depends on how many distinct values a column holds.

A Margin column on 50 million sales rows contains something close to 50 million distinct decimal values. It cannot compress. That one column can add more to your model than the entire fact table it was derived from. Worse, it's computed after data loads, so it doesn't benefit from the source-side compression tricks that make the imported columns cheap.

A measure costs nothing at rest. It's a line of text in the model.

The corollary: if you do need a derived column, compute it upstream — in SQL, in Power Query, in your warehouse. A column created in the source participates in the initial compression pass and typically lands substantially smaller than the same column created with DAX after the fact.

Which one, then

Reach for a measure when:

• the result is aggregated — sums, counts, averages, distinct counts
• the result is a ratio, rate, or percentage
• the value must respond to slicers and filters
• you need time intelligence — year-to-date, prior year, moving averages
• the value depends on what the user has selected

Reach for a calculated column when:

• you need to slice, filter, or group by the value
• you need it on an axis, in a legend, or in a slicer
• the value is a fixed property of the row that doesn't depend on filters
• you need it as a relationship key or a sort-by column

That first bullet is the decisive one. You cannot put a measure on an axis or in a slicer. If a user must be able to filter by "revenue band," that band has to exist as a column:

Revenue Band =
SWITCH(
    TRUE(),
    Sales[Amount] >= 100000, "Large",
    Sales[Amount] >= 10000,  "Medium",
    Sales[Amount] > 0,       "Small",
    "Zero"
)

Four distinct values across 50 million rows compresses to almost nothing, and it gives you something to slice by. That's a calculated column earning its place — low cardinality, needed for grouping, independent of filters.

Static classification versus dynamic

The comparison sharpens when the same requirement can go either way.

Classify customers as high or low value. As a column, the classification is fixed at refresh, based on each customer's lifetime total — a customer is "high value" everywhere in the report, and you can slice by it.

As a measure, the classification responds to context:

Value Segment =
VAR Revenue = [Total Revenue]
RETURN
    SWITCH(
        TRUE(),
        Revenue >= 1000000, "High",
        Revenue >= 100000,  "Medium",
        "Low"
    )

Now a customer can be "High" for 2026 and "Medium" for Q1, because the measure sees only the filtered rows. You can display this, but you cannot slice by it.

Neither is better. Ask whether the label is a property of the customer or a property of the selection — that determines the answer, and it's a question the stakeholder can settle in one sentence.

Practical guidance

Default to measures. They cost no memory, respect filters, and can't be misused on an axis. Write a calculated column only when you can name the thing you need to group or filter by.

Push columns upstream. If a derived column is genuinely needed, create it in SQL or Power Query rather than DAX. Smaller model, faster refresh.

Watch for high cardinality. A calculated column with near-unique values per row is nearly always a mistake. SELECTEDVALUE and a measure will usually do the job.

Use variables. VAR evaluates once and can be reused, which keeps repeated sub-expressions from being computed several times:

Margin % =
VAR TotalSales = SUM( Sales[Amount] )
VAR TotalCost  = SUM( Sales[Cost] )
RETURN
    DIVIDE( TotalSales - TotalCost, TotalSales )

Check the size. Open DAX Studio against the report and sort columns by size. Calculated columns you'd forgotten about show up immediately, and they're often near the top.

The rule that covers most cases: if it's a number you want to see, it's a measure. If it's a thing you want to group by, it's a column. Everything above is the reasoning behind that sentence — but the sentence alone will get you the right answer nearly every time.