📎 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
- 📗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.