The RANKX DAX function is used to rank items in a column based on a specified expression. This function comes under Statistical functions category.
It is often used in Power BI to create rank columns or measures that help you analyze data in terms of order, such as the top-performing products, regions, or employees.
Syntax:
RANKX(
table,
expression,
[value],
[order],
[ties]
)
Parameters
table: The table to rank.
expression: The value used to rank.
[value] (optional): An alternate value to use for ranking.
[order] (optional): Use ASC for ascending and DESC for descending (default is DESC).
[ties] (optional): Determines how to handle ties (default behavior assigns the same rank to ties).
Sales Dataset
| ProductID | ProductName | SalesAmount |
| P1 | Laptop | 1500 |
| P2 | Phone | 1200 |
| P3 | Tablet | 800 |
| P4 | Monitor | 900 |
| P5 | Keyboard | 300 |
| P6 | Mouse | 900 |
| P7 | Speaker | 1200 |
Following these steps-
Step-1: Create one measure to get the total sales.
Right-click on the dataset name and select New Measure.
Total Sales = SUM(Sales[SalesAmount])
Step-2: Create another measure to rank the products based on their SalesAmount in ascending order.
Product Rank = RANKX( ALL(Sales), [Total Sales], , ASC, Dense )
Now, you can see the rank results in ascending order. If you want to see the results in descending order, you can use DESC instead of ASC.
Dense: For products with the same sales amount, the rank will be the same, and the next rank will continue sequentially.
RANKX with SKIP:
Product Rank = RANKX( ALL(Sales), [Total Sales], , ASC, Skip )
Skip : If two or more values are tied, they will receive the same rank, but the next rank’s will be skipped.
For example, if two products are tied for rank 1, the next product will be ranked 3, not 2.
Refer more DAX function – DAX Tutorials
