The RANKX function returns the position or rank of each value resulting from applying an expression to the rows of a table comparing them with each other.
RANKX(
table,
expression
[, value
[, order
[, ties]]]
)
- table: Table containing the rows for which the expression will be evaluated.
- expression: Expression to evaluate for each row of the table.
- value: Optional argument. Scalar or expression that returns a scalar that you want to find in the list of values to be analyzed.
- order: Optional argument. A value that specifies how to apply a range to a value (sorting the list low to high or high to low).
- ties: Optional argument. Argument that determines how to apply ranges when there are ties in the list.
The RANKX function returns an integer.
Blank values are treated as zeros.
If the value argument is included, the function will return the range of that value in the list (and only the range of this value, not the range of the other values). If this value is not found in the list, RANKX includes it (temporarily) in the list and parses it again to get its position (see an example below).
If order is DESC, 0, or FALSE (or omitted), the range is applied in descending order. That is, the highest value in the list will receive the number 1. On the contrary, if order takes the value ASC, 1 or TRUE, the range is applied in ascending order. That is, the lowest value in the list will receive the number 1.
The ties argument determines how to apply ranges after identical values are found in the list. For example, if the five highest values in a list are 10, 9, 9, 9 and 8 (that is, if in position 2 there are three identical values), it will be this argument that determines which rank receives the value 8 located in the fifth position of our ordered list. If ties takes the value Skip (or if it is omitted), the next value after a tie (in our example, the number 8) will receive the range of the identical values (2 in our example, since they are the second highest value) plus the number of elements of the tie (2 + 3 = 5, in our example). On the contrary, if ties takes the value Dense, the next value after a tie will receive the next rank to that applied to the identical values. That is, in our example, the value 8 would receive the rank 3 (there would be a value with rank 1 -the 10-, three values with rank 2 -the three 9-, and a value with rank 3 -the 8-).
If you do not want to include one of the arguments, you can add the comma that precedes it and not add any value for it.
In the event that the ranking for repeated elements is being calculated, the DAX machine will combine them into a single element and apply the highest ranking they have separately (see example below).
Suppose we have a sales list that includes the product sold and the number of units sold:
Let's start by defining a measure that calculates total sales:
Ventas totales = SUM(Ventas[Cantidad])
...and let us bring the Producto field and the newly created measure to a "Table" type display:
If we want to know which products are the best sellers -regardless of the context- and in what position they are ordered after this analysis, we could use the RANKX function:
Rango = RANKX(ALL(Ventas[Producto]), Ventas[Ventas totales])
If we add this measure to the previous table we obtain:
As can be seen, the RANKX function has applied the ranges correctly, giving the value 1 to the best-selling product (product B, with 7 units) and the value 4 to the least sold product (product D, with 2 units).
Note the need to include the ALL function in the definition of the Rango measure. Otherwise, when the DAX machine started with the first of the products (product A), the context would be limited to this product. That is, the Rango measure would be calculated in this context limited to the rows corresponding to product A. And the order occupied by the sales of product A in a list in which only this product is found is 1 (it is the best-selling product as it is the only one). And the same would happen with the other products: the rank would always be calculated considering a table in which only said product was found, so its rank would always be 1.
Now suppose that we want the ranges to be applied in the reverse order, that is, we want to give the value 1 to the least-sold product and 4 to the best-seller. For this, it would be enough to include the order parameter:
Rango = RANKX(ALL(Ventas[Producto]), Ventas[Ventas totales], ,1)
It can be seen how, in this case, the third argument is not included in the definition of the measure. If we update the previous table we obtain the following:
If, by reordering the ranges from highest to lowest, we want to know what position the product occupies that has had sales of 6 units, the measure would have to be modified as follows:
Rango = RANKX(ALL(Ventas[Producto]), Ventas[Ventas totales], 6)
We can take the measure to a "Card" type display to see the result:
Indeed, it was product C that, with sales of 6 units, occupies the second position.
Now let's imagine that we want to calculate the position of the product of which 5 units were sold (and we know that there is no product with this sales figure):
Rango = RANKX(ALL(Ventas[Producto]), Ventas[Ventas totales], 5)
Now the visualization above shows the following:
Basically, what the measure is telling us is "if there were a product for which sales of 5 units had been registered, it would occupy position 3 in the ranking".
As mentioned, in the case of calculating rankings for duplicate elements, the DAX machine combines them into a single element and applies the highest ranking they would have separately.
To see this, let's consider the list of customers (in which we have both the first name and the last name and full name) and the sales associated with each one. Assuming that the full name is in the Customer[Name] field and that sales are calculated in the [Sales] measure, we can evaluate the ranking of each customer with the following measure:
Rank = RANKX(ALL(Customer[Name]), [Sales])
If we take the field with the full name, sales and this ranking to a table, the result is the following:
We see how two clients have first names Alisha and receive 94 and 26 rankings.
If we replace the full name with the first name and modify the measure that calculates the ranking:
Rank = RANKX(ALL(Customer[FirstName]), [Sales])
...the result is as follows:
Indeed, the DAX machine has combined both names and has assigned the highest ranking value to the result: 26.
