RANKX

How to Use the Power BI DAX function RANKX

Syntax of the RANKX function

The syntax of the RANKX function is as follows:


RANKX ( ❰table❱, ❰expression❱, [❰value❱[, ❰ties❱] ])


* `❰table❱`: The table or table expression that contains the data to be ranked.

* `❰expression❱`: The expression on which the ranking is based.

* `❰value❱`: The value to be ranked. This is optional.

* `❰ties❱`: Specifies how to handle ties. This is optional.

Example of the RANKX function

Let's look at an example of how the RANKX function works. Suppose you have a table called "Sales" with the following data:

| Product | Sales |

|---------|-------|

| A | 100 |

| B | 200 |

| C | 300 |

| D | 300 |

| E | 400 |

To rank the products by sales, you would use the following DAX formula:


RANKX ( Sales, Sales )


This formula will return the following results:

| Product | Sales | Rank |

|---------|-------|------|

| E | 400 | 1 |

| C | 300 | 2 |

| D | 300 | 2 |

| B | 200 | 4 |

| A | 100 | 5 |

As you can see, the products are ranked based on their sales, with ties being handled by assigning the same rank to the tied values.

Using the RANKX function with filters

You can also use the RANKX function with filters to rank data based on specific criteria. For example, suppose you want to rank the products by sales in the year 2021. You would use the following DAX formula:


RANKX ( FILTER ( Sales, Sales[Year] = 2021 ), Sales )


This formula filters the Sales table to include only the data from the year 2021 and then ranks the products based on their sales for that year.

Handling ties with the RANKX function

By default, the RANKX function handles ties by assigning the same rank to tied values. However, you can specify how ties are handled by using the `❰ties❱` argument. This argument can take one of three values:

* 0 - Ranks are assigned in the order in which they appear in the table.

* 1 - Ranks are assigned based on the average of the ranks that would have been assigned to the tied values.

* -1 - Ranks are assigned based on the minimum of the ranks that would have been assigned to the tied values.

For example, suppose you want to rank the products by sales and handle ties by assigning the minimum rank to tied values. You would use the following DAX formula:


RANKX ( Sales, Sales, , -1 )


The RANKX function is a powerful tool for ranking data in Power BI. With this function, you can easily rank data based on different criteria and handle ties in a variety of ways. By using the examples and syntax provided in this article, you'll be able to use the RANKX function to its full potential in your own Power BI projects.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

24-26 Oct 23 (London or Online)

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)