RANKX
Returns the rank of the current item among the rows of a table, based on an expression.
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
- table: the set to rank over, such as
ALLSELECTEDorALLof a column. - expression: the value to rank, usually a measure.
- value (optional): the value for the current item, if it differs from the expression. Leave it empty to use the expression.
- order (optional):
DESC, the default, orASC. - ties (optional):
DENSEorSKIP. Dense gives 1, 2, 2, 3 for a tie. Skip gives 1, 2, 2, 4. - blank (optional): controls the result for blank values.
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
- The table decides the answer.
ALLignores slicers, andALLSELECTEDrespects them. - Ranking in a card gives the rank of the whole visible selection, not of a row. Check the visual you put it in.
- Choose
DENSEorSKIPon purpose. The default may not match how the report is read. - Rank numbers are not additive. Do not add ranks together.
- A blank measure can give a blank rank. Test the rows with no sales.
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.