Module 5: Filter context and CALCULATE · Lesson 5.6 · about 16 min
Ranking and top N
Rank products, stores or days with RANKX, return the top N with TOPN, and avoid the classic traps with the total row, ties and filters.
Module 5 · about 16 minutes · read first: 5.3 and 5.5. You’ll be able to rank items and return the top N in measures and queries.
1. Why this matters
“Top 10 products”, “bottom 5 stores”, “where does this store rank?” appear in nearly every dashboard and in many interview exercises.
2. RANKX
Write Product Rank: each product's rank by Total Sales among all products (1 = best), with RANKX(ALL(Product[Product]), [Total Sales]).
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Product Rank = RANKX(ALL(Product[Product]), [Total Sales]) RANKX(table, expression, [value], [order], [ties]) evaluates the expression for each row of the table (with context transition), then finds where the current value fits. Three things to remember:
- Widen the table:
ALL(Product[Product]), orALLSELECTED(Product[Product])to rank among what’s selected in a slicer. - Order: descending by default (biggest = 1). Use
ASCfor “lowest cost = 1”. - The total row gets rank 1 here, because the grand total is bigger than any product. Hide it with
IF(ISINSCOPE(Product[Product]), RANKX(…)), which returns blank outside product rows.
3. TOPN
Write a query that returns the top 3 products by Total Sales, with their sales, sorted from best. Use TOPN and ADDCOLUMNS.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
EVALUATE
TOPN(3, ADDCOLUMNS(VALUES(Product[Product]), "Sales", [Total Sales]), [Sales])
ORDER BY [Sales] DESC TOPN(n, table, orderBy, [order]) returns the top rows as a table. In a measure, use it as a filter:
Top 3 Products Sales =
CALCULATE([Total Sales], TOPN(3, ALL(Product[Product]), [Total Sales]))
In a report, the visual-level Top N filter (Filters pane > Filter type > Top N) does the same without DAX, which is often simpler. DAX is needed when the top N must feed another calculation, such as “share of sales from the top 3 products”.
Principle
Principle: with the default ties setting, the rank of a value among values (descending) is
so ties share a rank and the next one skips. DENSE counts distinct larger values instead.
Try it: sales 90, 70, 70, 40. Default ranks: 1, 2, 2, 4. Dense: 1, 2, 2, 3.
4. Worked example
Rank stores within the selected region. Store Rank = IF(ISINSCOPE(Store[Store]), RANKX(ALLSELECTED(Store[Store]), [Total Sales])). With the Region slicer on West, Falls Centre is 1, Harbour Front 2, Forks Market 3; clear the slicer and Queen Street jumps to 1. ALLSELECTED makes the ranking follow the slicer, and ISINSCOPE hides the meaningless total.
5. Practice
- Write
Store Rankamong all stores (ignoring slicers). - Write a query for the 2 lowest-selling products.
- Why might a top 3 table show 4 rows?
Answers
RANKX(ALL(Store[Store]), [Total Sales]).EVALUATE TOPN(2, ADDCOLUMNS(VALUES(Product[Product]), "Sales", [Total Sales]), [Sales], ASC).- A tie at third place: TOPN keeps every row tied at the cut-off.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.