DAX Practice Exercises: 10 Questions on a Sales Model
· 8 min read
These DAX practice exercises use a tiny version of Brightline Supply Co., a fictional company that sells office, kitchen and cleaning supplies to businesses. The dataset is small enough to type into Power BI Desktop with Home > Enter data in a few minutes, and every question has an expected result, so you know when your measure is right. Try each one before you look at the solutions at the end.
-- Your first measure: everything else builds on it
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )The practice dataset
Create four tables. This is a star schema: Sales is the fact table, the other three are dimensions.
| OrderID | OrderDate | ShipDate | CustomerID | ProductID | Quantity | UnitPrice | DiscountPct | Status | Channel |
|---|---|---|---|---|---|---|---|---|---|
| 50001 | 2025-01-06 | 2025-01-08 | 1001 | 201 | 4 | 24.00 | 0.00 | completed | Online |
| 50002 | 2025-01-14 | 2025-01-16 | 1002 | 101 | 10 | 5.50 | 0.10 | completed | Sales Rep |
| 50003 | 2025-01-30 | 2025-02-03 | 1003 | 303 | 3 | 25.00 | 0.00 | completed | Online |
| 50004 | 2025-02-11 | 2025-02-13 | 1001 | 103 | 2 | 59.00 | 0.05 | completed | Partner |
| 50005 | 2025-02-24 | 2025-02-26 | 1004 | 301 | 6 | 14.00 | 0.00 | cancelled | Sales Rep |
| 50006 | 2025-03-05 | 2025-03-07 | 1002 | 204 | 5 | 19.90 | 0.00 | completed | Online |
| 50007 | 2025-03-27 | 2025-04-01 | 1004 | 303 | 8 | 25.00 | 0.10 | completed | Partner |
| 50008 | 2025-03-31 | 2025-04-02 | 1003 | 201 | 2 | 24.00 | 0.00 | refunded | Online |
| ProductID | ProductName | Category | UnitCost |
|---|---|---|---|
| 101 | Copy Paper A4 (500) | Paper & Print | 3.10 |
| 103 | Toner Cartridge Black | Paper & Print | 28.00 |
| 201 | Espresso Beans 1kg | Coffee & Kitchen | 11.50 |
| 204 | Tea Assortment (200) | Coffee & Kitchen | 9.40 |
| 301 | All-Purpose Cleaner 5L | Cleaning | 6.70 |
| 303 | Paper Towels (24) | Cleaning | 12.40 |
| CustomerID | CustomerName | Segment | Region |
|---|---|---|---|
| 1001 | Harbor & Pine Hotels | Enterprise | West |
| 1002 | Lakeside Studios | SMB | East |
| 1003 | Pioneer Studios | Mid-Market | North |
| 1004 | River Print Co | SMB | West |
Date: create it with DAX (Modeling > New table), then mark it as a date table:
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" )
)Set the data types (dates as Date, prices as Decimal number), sort Month by Month Number, and create the relationships in model view:
- Products[ProductID] to Sales[ProductID], one to many
- Customers[CustomerID] to Sales[CustomerID], one to many
- 'Date'[Date] to Sales[OrderDate], active
- 'Date'[Date] to Sales[ShipDate], inactive
If you want the background first: how to create a date table.
DAX practice questions
Brightline's rule: revenue counts completed orders only. Questions 1 and 2 show why that rule matters.
Exercise 1: Revenue, all statuses
Write Revenue as quantity × price × (1 − discount), summed over all lines.
Expected: 744.10 on a card.
Hint: the multiplication has to happen per row.
Exercise 2: Completed revenue
Write Completed Revenue that keeps only Status = "completed".
Expected: 612.10.
Hint: one function changes the filter context.
Exercise 3: Completed revenue by category
Put Products[Category] on a table visual with your measure. Expected: Coffee & Kitchen 195.50, Paper & Print 161.60, Cleaning 255.00. Hint: no new DAX needed. If the numbers are all 612.10, check the relationship.
Exercise 4: Percent of total by category
Write % of Completed Revenue so the three categories add up to 100%.
Expected: Coffee & Kitchen 31.9%, Paper & Print 26.4%, Cleaning 41.7%, total 100%.
Hint: the denominator needs the category filter removed. Remove it from the right column.
Exercise 5: Completed orders and average order value
Write Completed Orders and Average Order Value.
Expected: 6 orders, average order value 102.02.
Hint: divide revenue by a count, and protect against dividing by zero.
Exercise 6: Gross margin
Write Cost (quantity × the product's unit cost, completed only), Gross Margin and Gross Margin %.
Expected: Cost 316.40, Gross Margin 295.70, Gross Margin 48.3%.
Hint: the unit cost lives in Products. You need a function that follows the relationship from each Sales row.
Exercise 7: Year to date
Write Completed Revenue YTD and show it with 'Date'[Month] on the rows.
Expected: Jan 220.50, Feb 332.60, Mar 612.10.
Hint: use the Date table's column, not Sales[OrderDate].
Exercise 8: Revenue by ship date
Write Revenue by Ship Date using the inactive relationship.
Expected: Jan 145.50, Feb 187.10, Mar 99.50, Apr 180.00.
Hint: the relationship already exists. You only activate it inside one function.
Exercise 9: Customer tiers
Write Customer Tier: "Gold" for completed revenue of 200 or more, "Silver" for 100 or more, otherwise "Bronze". Leave the total row blank.
Expected: Harbor & Pine Hotels Gold (208.10), Lakeside Studios Silver (149.00), Pioneer Studios Bronze (75.00), River Print Co Silver (180.00).
Hint: the order of the conditions matters. More in the DAX IF statement and SWITCH.
Exercise 10: Rank customers
Write Customer Rank by completed revenue.
Expected: Harbor & Pine Hotels 1, River Print Co 2, Lakeside Studios 3, Pioneer Studios 4.
Hint: if every customer ranks 1, the ranking only sees one customer per row.
Bonus: audit an AI assistant's measure
An AI assistant wrote this for exercise 1. What does it return on a card, why does it look right on some rows of a table by OrderID, and what is the fix?
Revenue =
SUM ( Sales[Quantity] ) * SUM ( Sales[UnitPrice] ) * (1 - SUM ( Sales[DiscountPct] ))Expected: 5,892.00 on the card, against the correct 744.10. Check it in your model before reading the solution.
Solutions
Try first. Each solution has a one-line explanation, and the linked guides go deeper.
-- 1
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )
-- 2
Completed Revenue =
CALCULATE ( [Revenue], Sales[Status] = "completed" )
-- 4
% of Completed Revenue =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], REMOVEFILTERS ( Products[Category] ) ) )
-- 5
Completed Orders =
CALCULATE ( DISTINCTCOUNT ( Sales[OrderID] ), Sales[Status] = "completed" )
Average Order Value =
DIVIDE ( [Completed Revenue], [Completed Orders] )
-- 6
Cost =
CALCULATE ( SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) ), Sales[Status] = "completed" )
Gross Margin = [Completed Revenue] - [Cost]
Gross Margin % = DIVIDE ( [Gross Margin], [Completed Revenue] )
-- 7
Completed Revenue YTD =
TOTALYTD ( [Completed Revenue], 'Date'[Date] )
-- 8
Revenue by Ship Date =
CALCULATE ( [Completed Revenue], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )
-- 9
Customer Tier =
IF (
ISINSCOPE ( Customers[CustomerName] ),
SWITCH (
TRUE (),
[Completed Revenue] >= 200, "Gold",
[Completed Revenue] >= 100, "Silver",
"Bronze"
)
)
-- 10
Customer Rank =
RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue] )- 1: SUMX multiplies per row, then adds.
SUM ( Quantity ) * SUM ( UnitPrice )would be wrong: see SUM vs SUMX. - 2 and 4: CALCULATE replaces the Status filter. REMOVEFILTERS clears the category filter for the denominator: see DAX CALCULATE explained.
- 5: DISTINCTCOUNT counts orders, not lines, so it stays right when an order has several lines. 612.10 / 6 = 102.02.
- 6: RELATED needs a row context, which SUMX provides.
- 7 and 8: time intelligence and USERELATIONSHIP both need the Date table. Order 50003 moves from January to February by ship date, and 50007 moves from March to April.
- 9: with
>= 100first, Harbor would be Silver. ISINSCOPE blanks the total row. - 10: ALL lets RANKX compare against every customer, not only the one in the current row.
- Bonus: the measure adds first and multiplies after. On the card it computes 40 units × 196.40 (the sum of eight prices) × (1 − 0.25). In a table by OrderID, each order has one line here, so every row looks correct and only the total is wrong. The fix is the SUMX in solution 1.
Common mistakes
These are the errors that show up most when people (and AI assistants) do these exercises:
- Forgetting the business rule. 744.10 is a correct sum, but it is not Brightline's revenue. Always ask which rows count before you write the measure.
- Removing the wrong filter in exercise 4.
ALL ( Products[ProductName] )with Category on the rows gives 100% on every row, because the Category filter is still there. The column inside ALL or REMOVEFILTERS must match what the visual groups by. - Wrong condition order in exercise 9. AI assistants often list thresholds from low to high in SWITCH(TRUE()). SWITCH stops at the first TRUE, so nobody becomes Gold.
- COUNTROWS instead of DISTINCTCOUNT in exercise 5. In this dataset each order has one line, so both give 6. On real order-line data, COUNTROWS counts lines and the average order value comes out too low. The same trap exists in SQL: see COUNT DISTINCT in SQL.
- Not checking by hand. Pick one cell (Cleaning, or February) and add up the rows yourself. If your number differs from the visual, the measure is wrong, however clean it looks.
FAQ
Where can I practice DAX online for free?
You can practise DAX for free in Power BI Desktop, which is a free download for Windows, using a small dataset like the one above. Type it in with Enter data and check each measure against the expected results.
What are good Power BI practice datasets?
Look for a dataset with a clear star schema: one fact table of transactions and a few dimensions (products, customers, dates), plus a business rule or two (such as "completed orders only"). Small enough to check by hand is better for learning than millions of rows.
What DAX should I practise first?
SUMX, CALCULATE with filters, percent of total with REMOVEFILTERS, DIVIDE, a date table with TOTALYTD, RELATED, and SWITCH(TRUE()). These cover most junior analyst work and most interview questions: see Power BI interview questions for data analysts.
How do I know if my DAX measure is correct?
Compute one or two cells by hand from the source rows, or with a SQL query on the same data, and compare. Check the total row too. Then change a slicer and make sure the number moves the way you expect.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Units sold by region” →