RANK.EQ

The RANK.EQ function returns the rank or position of a value in a list of numbers.

Syntax

RANK.EQ(
    value,
    column
    [, order]
)

Parameters
  • value: Value whose range is to be obtained. It can be any DAX expression that returns a scalar.
  • column: Column from which to extract the range of value.
  • order: Optional argument that specifies how to apply the ranges, from the lowest to the highest value or vice versa.
Returned value

The RANK.EQ function returns an integer.

Additional Information

The column argument cannot refer to any column created with the ADDCOLUMNS, ROW, or SUMMARIZE functions.

If value is not present in column or is a Blank, the RANK.EQ function returns Blank.

The duplicate values receive the same rank (the highest of which they would receive if they received consecutive ranks), but the next value receives the rank it would receive if the aforementioned duplicate values had received different ranks. That is, if a value appears twice and receives rank 1, the next value will receive rank 3.

If order takes the value 0 or False (default value), the ranges are applied from highest to lowest. That is, the highest value will receive rank 1. On the contrary, if order takes the value 1 or True, the ranges are applied from lowest to highest, which implies that the lowest value will receive rank 1.

Examples

In this first example we start from the following table ("Data"):

Data table

If we wanted to add a calculated column with the range of each number in the Value column, we could use the following expression:

Posición = RANK.EQ(Data[Valor], Data[Valor])

RANK.EQ function

We see that the value 8 (the highest) has received rank 1, 7 has received rank 2, etc. The value 2, included twice, does not receive ranks 5 and 6, only 5. But the next value, 1, receives rank 7.

In this second example we start from the following data table ("Data2"):

Data table

We want to add a calculated column in which the range of each value in the Value column is dumped taking the Reference column as a reference, for which we use the following code:

Posición = RANK.EQ(Data2[Valor], Data2[Referencia])

The result is shown in the following image:

RANK.EQ function

This time, the number 8 is not present in the Reference column, so the RANK.EQ function returns Blank for this value. The value 7 is in the first position of the Reference column (ordered from highest to lowest) and receives the rank 1. The number 5 is in the third position (after 7 and 6) and receives the rank 3, and so on.

Category
Statistical
Submitted by admin on Sat, 07/20/2019 - 14:40