Downloads
DAXRanking and windowsIntermediate

RANKX

Returns the rank of the current item among the rows of a table, based on an expression.

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

Returns: A whole number (the rank). Rank 1 is the highest, by default.

What it does

RANKX ranks the current item against the others in a table. In a visual that lists products, each row gets its position among the products by sales. The table you give is the set to rank against, so choose it deliberately. ALLSELECTED ranks the items the user selected. ALL ranks against everything. The order defaults to descending, so the highest value is rank 1.

Syntax and arguments

Example

The rank of each product by sales, among the products the user has selected. The empty third argument means the expression is used as the value.

Product Rank =
RANKX (
    ALLSELECTED ( Product[ProductName] ),
    [Total Sales],
    ,
    DESC,
    DENSE
)

In a table with one row per product, each row shows where that product stands among the selected products.

Example: a second use

The rank of each store by gross margin percent across all stores, ignoring the store slicer.

Store Margin Rank =
RANKX (
    ALL ( Stores[StoreName] ),
    [Gross Margin %],
    ,
    DESC,
    SKIP
)

ALL makes the ranking company-wide. SKIP means two stores that tie at second place are both 2, and the next store is 4.

Things to watch

In business terms

Vendor spend league tables, product margin rankings, branch performance ranks, and the rank step in a Pareto or ABC classification based on sales.

Related functions

All DAX functions · View as Markdown · DAX fundamentals