Ranks inside a category, blank in subtotals
In a Category › Product matrix, a rank makes sense on product rows. In a category subtotal, RANKX would compare the whole category with single products and always say 1.
ISINSCOPE(Product[Product]) is true only where the matrix groups by product. And ALL(Product[Product]) clears the product but keeps the category of the row, so products are ranked within their category.
Your task
Write Rank in Category: on product rows, the product’s rank by Sales Amount within its category; BLANK in category subtotals and the total.
About this exercise
You practise rankx with isinscope with RANKX, ISINSCOPE and IF. 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, Rank in Category, fills a Power BI-style matrix grouped by Product[Category] and Product[Product]. 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
Rank in Category =The result to build
| Category › Product | Rank in Category |
|---|---|
| Accessories | |
| Bike Lock | 2 |
| Helmet | 1 |
| Water Bottle | 3 |
| Bikes | |
| City Bike | 3 |
| Road Bike | 1 |
| Trail Bike | 2 |
| Clothing | |
| Cycling Jersey | 1 |
| Rain Jacket | 2 |
| Total |
Last updated 10 November 2026
Opening the exercise…