All lessons

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

Try it: write the DAX

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.

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:

  1. Widen the table: ALL(Product[Product]), or ALLSELECTED(Product[Product]) to rank among what’s selected in a slicer.
  2. Order: descending by default (biggest = 1). Use ASC for “lowest cost = 1”.
  3. 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

Try it: write the DAX

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.

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 v among values x1,…,xn (descending) is

rank(v)=1+#{i:xi>v}

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

  1. Write Store Rank among all stores (ignoring slicers).
  2. Write a query for the 2 lowest-selling products.
  3. Why might a top 3 table show 4 rows?
Answers
  1. RANKX(ALL(Store[Store]), [Total Sales]).
  2. EVALUATE TOPN(2, ADDCOLUMNS(VALUES(Product[Product]), "Sales", [Total Sales]), [Sales], ASC).
  3. A tie at third place: TOPN keeps every row tied at the cut-off.

Check yourself

1Why does RANKX(Product, [Total Sales]) often rank every product 1?
2Two products tie for 2nd place. With RANKX's default ties setting, what rank does the next product get?
3What does TOPN(3, VALUES(Product[Product]), [Total Sales]) return?

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.