Ranking products with RANKX
RANKX(table, expression) evaluates the expression for every row of the table, then ranks the value of the current cell among them: 1 for the largest.
The table must contain every product, so iterate ALL(Product[Product]). Iterating Product only sees the product of the row, and every product comes out first. The year column still filters, so the ranks differ per year.
Your task
Write Product Rank: each product’s rank by Sales Amount among all products, 1 for the best seller.
About this exercise
You practise ranks that respect the filter context with RANKX and ALL. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Product Rank, fills a Power BI-style matrix grouped by Product[Product] and Date[Year]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount. Reference them in square brackets, for example [Sales Amount].
Where you start
Product Rank =The result to build
| Year | |||
|---|---|---|---|
| Product | 2024 | 2025 | Total |
| Bike Lock | 7 | 6 | 7 |
| City Bike | 3 | 2 | 3 |
| Cycling Jersey | 5 | 5 | 5 |
| Helmet | 4 | 4 | 4 |
| Rain Jacket | 6 | 7 | 6 |
| Road Bike | 1 | 1 | 1 |
| Trail Bike | 2 | 3 | 2 |
| Water Bottle | 8 | 8 | 8 |
| Total | 1 | 1 | 1 |
Last updated 10 November 2026
Opening the exercise…