DAX for Beginners: Measures and Calculated Columns

DAX (Data Analysis Expressions) is the formula language of Power BI, Power Pivot and other Microsoft tools. It looks like Excel formulas, with one fundamental difference: it doesn't work cell by cell but with tables and columns, and it recalculates based on whatever filters the user has applied. Mastering measures is what turns a pretty report into an analytical one.

Calculated columns vs. measures

Rule of thumb: if the result is a number that will be aggregated, it's a measure. Calculated columns are for when you need a label on each row. And if you can create the column in Power Query, do it there instead.

Your first measure

In Report view, select the Sales table in the Data pane and click New measure. Type in the formula bar:

Total Sales = SUM ( Sales[Amount] )

• The measure's name goes to the left of the equals sign.
• Columns are written as Table[Column].
• DAX function names are always in English (SUM, AVERAGE, CALCULATE), whatever the interface language.
• This course uses commas between arguments, the default for U.S. English settings.

The measure appears with a calculator icon. Drag it into a card, a chart by city or a matrix by month: in each place it calculates for exactly what that spot represents.

Basic functions

We'll use this small sample Sales table (Amount = Quantity × Price):

SUMX: calculate row by row, then add up

If the table has no Amount column, only Quantity and Price, you can't write SUM(Quantity) * SUM(Price): you'd multiply the totals (61 × 35.30), which is meaningless. You need to multiply on every row and then add. That's what iterator functions, the ones ending in X, are for:

Total Sales =
SUMX ( Sales, Sales[Quantity] * Sales[Price] )

Total Cost =
SUMX ( Sales, Sales[Quantity] * Sales[Unit cost] )

SUMX walks through the Sales table, evaluates the expression on each row (45, 45, 72, 90, 45.60, 27) and adds them up: $324.60. Total cost is $234.50. AVERAGEX, MINX, MAXX and COUNTX work the same way.

Measures built on other measures

Once the base measures exist, build the rest on top of them. It's easier to read, and if the definition of sales changes, everything updates:

Gross Profit = [Total Sales] - [Total Cost]

Gross Margin % = DIVIDE ( [Gross Profit], [Total Sales] )

Avg Order Value = DIVIDE ( [Total Sales], [Orders] )

With the sample table: Gross Profit = 324.60 − 234.50 = $90.10; Gross Margin % = 90.10 ÷ 324.60 = 27.8%; Avg Order Value = 324.60 ÷ 6 = $54.10.

Always use DIVIDE instead of the / operator: if the denominator is zero or blank, DIVIDE returns a blank (or whatever you pass as the third argument) instead of an error.

Format each measure under Measure tools: Gross Margin % as a percentage with one decimal, sales as currency, and so on. The format travels with the measure to every visual.

The base measures on Summit's data

Summit's Sales table has no Amount or Cost column. It has Quantity and UnitPrice, and each product's cost lives in the Products dimension. That's typical of real systems, and here's how you handle it:

Total Sales =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Total Cost =
SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )

Orders = DISTINCTCOUNT ( Sales[OrderID] )

Lines = COUNTROWS ( Sales )

• RELATED fetches, for the row SUMX is currently on, a value from the table on the "one" side of the relationship (Products). It works because you created the relationship in Lesson 5; without it you get an error.
• Orders uses DISTINCTCOUNT because each order spans several rows, one per product. COUNTROWS counts lines, not orders.

Check your measures against these results for the full period (2025–2026, no filters):

Put them in a matrix by Category and the first business insight appears: the category that sells the least earns the best margin.

Beverages sells $152,775 (29.6% of the total) but keeps 32 cents of every dollar. The "(Blank)" row for the P99 sales shows a 100% margin because they have no cost: one more reason to fix them.

Filter context: the most important idea in DAX

Put the Total Sales measure in a matrix with Product on rows:

It's one formula, yet every row returns something different. Why? Because before calculating, Power BI applies that cell's filter context: on the "Rice" row, the Sales table is filtered to rice rows and SUMX walks only those. The Total row has no product filter, so it walks them all. Filter context comes from the visual's rows and columns, slicers, page and report filters, and cross-filtering from other visuals.

Implicit vs. explicit measures

When you drag a numeric column into a chart, Power BI creates an implicit measure (for example, "Sum of Amount"). It works to get started, but you can't reuse it in other formulas or control its format in one place, and some advanced features don't support it. The professional practice is to create explicit measures for everything you display.

Common beginner DAX mistakes

Total Sales     = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )
Total Cost      = SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )
Gross Profit    = [Total Sales] - [Total Cost]
Gross Margin %  = DIVIDE ( [Gross Profit], [Total Sales] )
Orders          = DISTINCTCOUNT ( Sales[OrderID] )
Customers       = DISTINCTCOUNT ( Sales[CustomerID] )
Avg Order Value = DIVIDE ( [Total Sales], [Orders] )
Units           = SUM ( Sales[Quantity] )
Avg Unit Price  = DIVIDE ( [Total Sales], [Units] )

Summary

• DAX works with tables and columns; function names are always in English.
• Measures for numbers that get aggregated; calculated columns only to classify rows.
• SUM, AVERAGE, COUNTROWS, DISTINCTCOUNT and DIVIDE cover the basics; SUMX calculates row by row.
• Build measures from other measures and format them once.
• Filter context gives every cell its own result.
• RELATED brings dimension values into SUMX; DISTINCTCOUNT counts orders, not lines.

Next lesson: CALCULATE and Filter Context in DAX. You'll learn CALCULATE, the most important function in DAX, which lets you change the filter context.