📎 This article includes 2 downloadable practice files ↓

📘 Power BI & DAX Beginner Course · Lesson 5 of 10

With a good model, most measures are short. This lesson builds the core set; check each one against the expected-results workbook.

Sales, cost, margin

Total Sales = SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
Total Cost  = SUMX ( Sales, Sales[Qty] * RELATED ( Products[Cost] ) )
Margin      = [Total Sales] - [Total Cost]
Margin %    = DIVIDE ( [Margin], [Total Sales] )

SUMX goes row by row through Sales, multiplies, then adds up: an iterator, like SUMPRODUCT. Measures can use other measures in square brackets, which keeps formulas short and consistent.

If your Sales table already has an Amount column, Total Sales = SUM ( Sales[Amount] ) is enough.

Counting

Orders              = COUNTROWS ( Sales )
Customers Who Bought = DISTINCTCOUNT ( Sales[CustomerID] )
Avg Order Value     = DIVIDE ( [Total Sales], [Orders] )

DIVIDE, not /

DIVIDE(a, b) returns blank (or a value you choose as the third argument) when b is zero, instead of an error. Use it for every ratio.

Variables: VAR … RETURN

Margin % (v) =
VAR Sales_ = [Total Sales]
VAR Cost_  = [Total Cost]
RETURN DIVIDE ( Sales_ - Cost_, Sales_ )

Variables make long measures readable and calculate each part once.

Test in a table visual

Put Products[Category] in a table with all the measures. Each row calculates for its category, and the total row calculates for everything, not by adding the rows. That’s filter context, the heart of DAX (Lesson 6).

Where beginners go wrong

Practice

Download the data model and expected results below. Load all four tables into Power BI Desktop and build this lesson’s steps; compare your numbers with the expected-results workbook.