RANKX

* `❰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:

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:

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:

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

Contact Us