Power Pivot DAX measures for beginners, step by step

You drop Revenue into a PivotTable and the totals look right. Then you add a profit-margin column, and the grand total reads 43% while every row above it says something different. Nothing is broken. You have just met the gap between a column that adds up and a calculation that has to re-read the data for each cell it lands in. That second thing is a measure, and Power Pivot’s DAX language is how you write one.

This walkthrough builds your first explicit measure from scratch, then covers the three traps that trip up everyone in week one: SUM versus SUMX, filter context, and grand totals that quietly lie.

What a measure actually is

A measure is a named formula that recalculates for every cell of a PivotTable, using only the rows that cell’s filters allow through. It stores no values. A calculated column does the opposite: it runs once per row when the data loads, then sits there as a stored column you can drag around like any other field.

That difference decides almost everything else. A column knows about one row at a time. A measure sees a whole filtered set and collapses it into a single number. Microsoft’s reference on DAX in Power Pivot draws the same line: a calculated column is evaluated row by row, a measure is evaluated in the context of the PivotTable cell.

Create your first explicit measure

Start with a tiny Sales table loaded into the data model. Three regions, units sold, and a unit price. You want total revenue, which the source data never stores as its own column.

1. In Power Pivot, click an empty cell in the Calculation Area below your table.
2. Type the measure name, then :=, then the formula.
3. Press Enter, then drag the measure into a PivotTable’s Values area.

Here is the measure itself. The name goes before the := assignment operator, which is what marks this as an explicit measure rather than a stray calculation.

Total Revenue := SUMX(Sales, Sales[Units] * Sales[Price])

SUMX walks the Sales table row by row, multiplies Units by Price for each one, and adds the results. With the rows above, a PivotTable cell with no region filter returns 2,070. Filter to West and the same measure returns 1,710, because filter context narrowed the rows it iterates. You never edited the formula; the PivotTable cell changed what the formula could see.

Name measures in plain words, not Measure 1. Set the number format once on the measure itself, in the Power Pivot field settings, and every PivotTable that uses it inherits the format. That alone is a reason to write an explicit measure instead of dragging a raw field into Values.

SUM versus SUMX, the trap everyone hits

New users reach for SUM because it is familiar, then wonder why they cannot multiply two columns together inside it. SUM takes one column and adds it. It has no idea how to read Units and Price on the same row.

Total Units := SUM(Sales[Units])
Total Revenue := SUMX(Sales, Sales[Units] * Sales[Price])

The first line adds a single column, which is all SUM can do. The second uses an iterator: SUMX evaluates the expression once per row, holds each result, then totals them. Any function ending in X (SUMX, AVERAGEX, MINX) is an iterator. Reach for one whenever a calculation needs more than one column on the same row.

Try to feed two columns to a plain aggregator and DAX stops you with a message like “A single value for column ‘Price’ cannot be determined.” That error is the language telling you to switch to the iterator form. It is not a bug in your data; it is the wrong tool for a row-by-row job.

CALCULATE rewrites the filter

Filter context is the rule that a measure only sees the rows the current PivotTable cell allows. CALCULATE is the one function that can change those rules. It takes a measure, then one or more conditions that overwrite the incoming filter.

Revenue West := CALCULATE([Total Revenue], Sales[Region] = "West")

The first argument is the number to compute. Every argument after it edits the filter before that number is worked out. So Revenue West ignores whatever region a row is showing and forces the calculation onto West only. Put it in a PivotTable broken out by month and you get West revenue per month, side by side with the unfiltered total.

This is also how you build comparisons. A measure for last year’s revenue, a measure for this year’s, and a third that divides one by the other gives you growth percentages that follow every slicer on the page. Each one is a short CALCULATE wrapped around a base measure you already wrote, which is why getting the first measure right pays off more than once.

When the grand total quietly lies

Here is where the margin example from the top goes wrong. Drag a Margin field straight into Values and Excel builds an implicit measure for you, usually Average of Margin. Each row averages fine. The grand total averages the averages, which is not the blended margin and is rarely the number anyone wants.

The fix is to compute the ratio from its parts at the total level, with an explicit measure built on two other measures.

Average of Margin

Margin % := DIVIDE([Total Profit], [Total Revenue])

Use DIVIDE rather than the / operator. It returns a blank instead of an error when the denominator is zero, so an empty region does not break the whole column.

Measure or calculated column?

The choice is not about preference; it follows from what the formula needs to see. If you are unsure, default to a measure: it stays out of your file size and it responds to slicers. Reach for a calculated column only when you need the value to exist on every row, usually to group or filter by it.

• ✓ Aggregating numbers for a PivotTable, chart, or KPI card → measure
• ✓ A ratio, percentage, or running total that must respond to slicers → measure
• ✓ A value you want to slice, sort, or filter by (a price band, a fiscal quarter) → calculated column
• ✓ A row-level flag feeding another formula → calculated column

This is the same split that decides when to summarize data in a PivotTable rather than a flat formula, just one layer deeper in the data model. If your source data needs cleaning before any of this, do it in Power Query first so the model loads tidy. For the practical resource on filter context, Chandoo’s introduction to DAX formulas and measures works through more examples.

Start with one measure

You do not need twenty functions to get value from Power Pivot. Three carry most beginner work: an aggregator for plain totals, an iterator for row-by-row math, and CALCULATE for everything filtered. Build a single explicit measure on real data and watch it recalculate as you add slicers. Once that clicks, the rest of DAX is variations on the same idea.

1. Load one table into the data model and write Total Revenue with SUMX.
2. Add a region slicer and confirm the number reacts.
3. Replace any implicit ratio with an explicit DIVIDE measure.

If you have already built a standard PivotTable, you have done the hard part of thinking in rows and fields. Measures just hand that thinking to a formula.