
π This article includes 2 downloadable practice files β
In this article
DAX gives you two ways to calculate: calculated columns and measures. They look similar and behave completely differently. Knowing which to use saves hours of confusion.
Calculated column
Table tools βΊ New column on the Sales table:
Line Amount = Sales[Qty] * RELATED ( Products[Price] )
- Calculated once per row when data refreshes, and stored in the model.
- Can be used in slicers, rows and axes.
RELATEDfetches a value from the βoneβ side of a relationship, like VLOOKUP.
Measure
Home βΊ New measure:
Total Sales = SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
- Calculated when a visual needs it, for whatever filters apply: the whole year, North only, Laptops in May.
- Not stored per row; takes no space.
- Goes in the Values well of visuals.
Which to use
| Use a column when⦠| Use a measure when⦠|
|---|---|
| You need to slice or group by the result (Size band, FY) | You want a number that sums, averages, compares |
| It describes a single row | It depends on what’s selected |
| Example: Price band = IF(Price > 10000, “Premium”, “Standard”) | Example: Total Sales, Margin %, YoY growth |
Why ratios must be measures
If each row has a margin %, adding them across 600 rows gives thousands of percent. The right answer is total margin Γ· total sales for whatever is selected, which only a measure calculates correctly.
Organise measures
Create an empty table to hold measures (Home βΊ Enter data, name it β_Measuresβ), then set each measure’s Home table to it. All measures appear together at the top of the field list.
Where beginners go wrong
| Mistake | Result |
|---|---|
| Percentage as a calculated column | Totals wrong |
| Dragging a numeric column and accepting “Sum” | Works, but implicit; write explicit measures |
| Hundreds of calculated columns | Big, slow model; push row logic to Power Query |
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.
π Practice files for this article
β¬ Download all 2 files (ZIP Β· 37 KB)
- πPractice data model (Excel)Four tables: Sales (600 orders, FY 2024-25 and 2025-26), Products, Customers and a Calendar with FY columns.β¬ XLSX Β· 40 KB
- πExpected resultsThe values your measures and visuals should show.β¬ XLSX Β· 7 KB
Free to use for learning. Files with macros (.bas) are plain text β import them with Alt+F11 β File β Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.