📎 This article includes 3 downloadable practice files ↓
Functions ending in X walk through a table row by row, calculate an expression for each row, then aggregate.
Total Profit = SUMX ( Sales, Sales[Amount] - Sales[Cost] )
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Avg Order Value = AVERAGEX ( Sales, Sales[Amount] )
Avg Sales per Customer = AVERAGEX ( VALUES ( Customers[Customer] ), [Total Sales] )
Customer Profit Rank = RANKX ( ALL ( Customers[Customer] ), [Total Profit], , DESC, DENSE )Practice model: profit ₹72,60,142 (25.8% margin); average order ₹40,135; average per customer ₹28,09,458. Top three customers by profit: Nair Supplies, Sharma Traders, Kapoor Retail.
Average per order vs per customer
The two averages differ because they iterate different tables. AVERAGEX over Sales averages orders; over VALUES(Customers[Customer]) it averages customer totals. Decide which question you’re answering before writing the measure.
Customer Profit Rank as a visual-level filter “is less than or equal to 5”. The ranking recalculates with every slicer.RANKX(Customers, ...) without ALL ranks each customer against itself (always 1) when customers are on rows. ALL removes that row filter for the comparison.Practice
Import pbi-intermediate-model.xlsx into Power BI Desktop (free), create the relationships listed at the top of the .dax file, mark the Date table as a date table and add this lesson’s measures. Compare your card and table values with the lesson’s sheet in the expected-results file. Those numbers were worked out independently in Python from the same data, so a mismatch means a filter or relationship to check.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 61 KB)
- 📗Practice model (Excel)Two years of sales (700 orders, FY 2024-25 and 2025-26) with Customers, Products, a full Date table, Targets and RegionUsers.⬇ XLSX · 63 KB
- 📄Lesson 4 measures (.dax)Every measure from this lesson, ready to paste into Power BI Desktop.⬇ DAX · 785 B
- 📗Expected resultsThe numbers your measures should show, one sheet per lesson.⬇ XLSX · 11 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.