Ravindra BagaleCourses & study guides

13. DAX: Data Analysis Expressions

13.17 Ranking: RANKX and TOPN

By Last updated:

RANKX

RANKX(<table>, <expression>, [value], [order], [ties])

Product Rank =
IF(
    HASONEVALUE(Product[Product Name]),
    RANKX(ALL(Product[Product Name]), [Total Sales], , DESC, DENSE)
)

-- Rank dark stores by speed: fastest (lowest time) = rank 1
Store Speed Rank =
IF(
    HASONEVALUE(DarkStore[Store Name]),
    RANKX(ALL(DarkStore[Store Name]), [Avg Delivery Time (mins)], , ASC, DENSE)
)

Customer Rank in City =
RANKX(ALLSELECTED(Customer[Customer Name]), [Total Sales], , DESC)
  • Use ALL(...) so every item is ranked against all items, not only itself.
  • HASONEVALUE hides the meaningless rank on the total row.
  • Order: DESC (highest = 1) or ASC (lowest = 1, useful for delivery time).
  • Ties: SKIP (default: 1, 2, 2, 4) or DENSE (1, 2, 2, 3).

TOPN

TOPN(<n>, <table>, <orderBy expression>, [order]) returns a table with the top N rows.

Top 5 Products Sales =
CALCULATE(
    [Total Sales],
    TOPN(5, ALL(Product[Product Name]), [Total Sales], DESC)
)

Top 5 Share % = DIVIDE([Top 5 Products Sales], CALCULATE([Total Sales], ALL(Product)))

-- Calculated table of the 10 busiest dark stores
Top 10 Stores =
TOPN(
    10,
    ADDCOLUMNS(VALUES(DarkStore[Store Name]), "Orders", [Total Orders]),
    [Orders], DESC
)

Top N without DAX

For a simple "show fakt the top 10 products in this chart", use the Top N filter in the Filters pane (Module 22). Use DAX TOPN when you need a number (like the share of the top 5) or a table.

Ravindra Bagale's Tip

Friends, many students use RANKX(Product, …) and get rank 1 for every row, because the table argument still has the current filter. Use ALL(Product[Product Name]) or ALLSELECTED as the table, and remember that RANKX ties are Skip by default, so pass Dense if you want 1, 2, 2, 3. Never forget this.