8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.12 Calculated Columns vs Measures in Depth

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

Salman builds a fictional Nagpur dark-store report for class. He needs Net Sales by City and % of orders under 10 minutes. He creates two calculated columns: Net Amount and Profit %. Net Amount + SUM works. Profit % averaged across cities is wrong. Interviewers ask him this every time — this section is the full answer.

Explain simply

• Calculated column: new column; DAX runs once per row at refresh; result is stored.
• Measure: named formula; not stored; runs at query time in the filter context of each visual cell.
• Same formula can give different results in the two places because row context ≠ filter context (Module 13.3).

Show the data

Fictional sample (src/practice/column_vs_measure_orders.csv):

About this data: This table uses columns: Order ID, City, Amount, Discount, Delivery Time Mins, Order Status. This table uses columns: City, Net Sales (₹), % Under 10 (delivered). This table uses columns: Calculated column, Measure. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Hand: Net all = 1750 (1,900 − 50 − 100); under-10 among delivered = 2 of 4 = 50%.

Demonstrate

% Under 10 Mins =
DIVIDE(
    CALCULATE(DISTINCTCOUNT(Orders[Order ID]), Orders[Order Status] = "Delivered", Orders[Delivery Time Mins] <= 10),
    CALCULATE(DISTINCTCOUNT(Orders[Order ID]), Orders[Order Status] = "Delivered")
)

The same logic implemented both ways

Goal: show Net Sales (Amount − Discount) by City.

-- Option A: calculated column + sum
Net Amount = Orders[Amount] - Orders[Discount]
Net Sales (A) = SUM(Orders[Net Amount])

-- Option B: measure only (iterator), nothing stored
Net Sales (B) = SUMX(Orders, Orders[Amount] - Orders[Discount])

Both return the same number for every city. Option B uses no extra memory. Option A helps if you also filter or bucket by net per line.

Goal: % of orders delivered within 10 minutes — must be a measure (see formula above). A ratio stored as a column is wrong when summed or averaged across cities.

Show the result

Your result should look like this (fictional sample):

Explain what happened

Columns materialise row values. Measures recompute with whatever filters the cell has. Averaging percentages ignores different denominators; DIVIDE on totals uses the correct combined numerator and denominator.

Explore further

Fix common mistakes

Practise independently

Think like a professional

Memory: high-cardinality text columns on large facts hurt VertiPaq. Governance: one official measure for each KPI name. Interview: draw the decision table; say ratios and time intelligence are measures.

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards