RANKX
A value's rank among an expression's values over a table's rows: largest first by default, ties skipping the next ranks (SKIP) or not (DENSE).
Group: Aggregation. Compatible with DAX.
Syntax
RANKX ( <table>, <expression> [, <value>] [, <order>] [, <ties>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to rank among, usually ALL of a column so the ranking ignores the current filter on it. |
expression | Expression evaluated for each row | The value each row is ranked by. |
value | Value optional | The value whose rank is wanted. Left out, it is the expression in the current context. |
order | Keyword optional | DESC (largest first, when left out) or ASC (smallest first). |
ties | Keyword optional | SKIP (when left out): after two rows tied first, the next is third. DENSE: it is second. |
Returns
Whole number. The rank: 1 for the first.
Remarks
- The expression is evaluated for every row of the table, then once for the value; the value's rank is one more than the number of rows that come before it.
- The value need not be one of the rows' values: it is placed among them where it would fall.
- A blank compares as zero, so in a ranking from the largest down it sits between the positive and the negative values.
- Rank against
ALL ( column )(orALLSELECTED) when the rank is shown in a table grouped by that column; ranking against the filtered column itself gives every row rank 1.
Examples
Ranking products by liters sold
EVALUATE
ADDCOLUMNS (
VALUES ( Products[Product] ),
"Liters", [Liters sold],
"Rank", RANKX ( ALL ( Products[Product] ), [Liters sold] )
)
ORDER BY [Rank]| Products[Product] | Liters | Rank |
|---|---|---|
| Pils | 1004326 | 1 |
| IPA | 331884 | 2 |
| Cider | 301074 | 3 |
| Lettøl | 169179 | 4 |
SKIP and DENSE
Two shops tie for first. With SKIP the next is third; with DENSE it is second.
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
RETURN
ADDCOLUMNS (
Shops,
"SKIP", RANKX ( Shops, [Liters], , DESC, SKIP ),
"DENSE", RANKX ( Shops, [Liters], , DESC, DENSE )
)
ORDER BY [Liters] DESC, [Shop]| Shop | Liters | SKIP | DENSE |
|---|---|---|---|
| B | 80 | 1 | 1 |
| C | 80 | 1 | 1 |
| A | 50 | 3 | 2 |
| D | 20 | 4 | 3 |
Where a value would rank
60 is not among the values; it would come third.
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
RETURN
ROW ( "Rank of 60", RANKX ( Shops, [Liters], 60 ) )| Rank of 60 |
|---|
| 3 |
Related functions
RANK, RANK.EQ, TOPN, ALL, ALLSELECTED.