Power BI RANKX Function

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

RANKX is a type of function in Power BI. It is a built-in function termed a sorting function, which is used extensively in sorting the data in various conditions. The syntax for this function is as follows: RANKX(<table>, <expression>, <value>, <order>, <ties>).

RANKX Function in Power BI

The RANKX function in Power BI works similarly to the one in the excel RANK functionExcel RANK FunctionExcel Rank formula gives rank of a given data set of numbers. The rank function was an inbuilt function in Excel 2007 and older versions; we now have Rank.Avg and Rank.Eq functions in subsequent versions above 2007.read more and assigns rank based on numbers of the specific or mentioned column. Below is the syntax of the RANKX Power BI DAXPower BI DAXIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more function.

RANKX Function

Table: We need to specify based on which table you are ranking.

Expression: We must supply the column name here based on which column you are ranking.

Value: This is a bizarre argument unless you are ranking at the advanced level. This argument will not put to use. So don’t worry about this argument at this point in time.

Order: In this argument, we can mention whether the ranking is in the form of ascending or descending order. The default parameter is in descending order, i.e., it will rank the top value 1, and so on.

We can supply two arguments here: TRUE or FALSE. TRUE is for ascending order, i.e., the lowest value is 1. If you supply FALSE, it will rank in descending order. Therefore, the highest value is 1.

Ties: This is the important thing you need to learn. In this argument, we can specify what should be done if there is a TIE between two values.

  • If you SKIP this, the rank after the tied value will be the rank of the tied value plus a count of tied values. So, for example, if there are 3 values tied at 5th rank, then the next rank will be 8 = (5+3).
  • If you supply DENSE as the option, the next value after the tied rank will be the next rank of the tied values. So, for example, if there are 3 values tied at 5th rank, then the next rank will be 6 = (5+1).

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

Example of RANKX Function in Power BI

Below is an example of a RANKX function. To apply this function in Power BI, we have created the below data in Excel.

Power BI RANKX (Excel Data)

It is the data of marks obtained from 6 subjects. Then, based on different parameters, we will rank these students.

Copy and paste the data directly to Power BI. Else, you can copy it to an Excel file and then import it to Power BI as an Excel file reference. So, you can download the Excel workbook template from the link below, which is used for this example.

You can download this Power BI RANKX Excel Template here – Power BI RANKX Excel Template

We have directly uploaded the data to Power BI.

Power BI RANKX - Import Data

You can use the RANKX function in the following steps.

  1. Go to the “Report” view.

    Power BI RANKX - Report View

  2. In the “Fields” section, right-click on the table name and choose “New column.”

    Power BI RANKX - Add New Column

  3. First, we need to enter what should be the column name. We will name it “Rank By Total Score.”

    Power BI RANKX - Rename Column

  4. For this new column, open the Power BI RANKX function.

    Power BI RANKX - Rankx Function

  5. The table is the first parameter of this function, so our table name is “Table2,” and supplies the same.

    Power BI RANKX - Enter Table Name

  6. Expression is nothing but based on which column value we need to rank. Based on “Total,” we are ranking in this case so that we will supply the same.

    Power BI RANKX - Expression

  7. Close the bracket and press the “Enter” key to get this new calculated column in the table.

    Power BI RANKX - New Calculated Column

  8. Now, insert a blank “Table” visual to see how our RANKX formula works.

    Power BI RANKX - Insert Table From Visual

  9. Drag and drop “Student Name,” “Total,” and the newly calculated column, i.e., “Rank By Total Score,” to the “Values” field of the “Table” visual.

    Power BI RANKX - Enter Data in Values Field

  10. It will give us each student’s RANK against the total score.

    Power BI RANKX - Students Rank
    So, we have a ranking based on the scores of each student.

  11. By using the RANK column, we can sort the data. Place a cursor on a rank column to see a small down arrow key.

    Power BI RANKX - Select Rank Column

  12. Now, assume you want to rank students based on “Sub3” scores. For this, insert one more column.

    Power BI RANKX - New Column

  13. Once again, open the Power BI RANKX function and supply the TABLE name.

    Power BI RANKX - Enter Table Name (To Rank by Sub3)

  14. For Expression, instead of the “Total” column, supply the “Sub 3” column.

    Power BI RANKX - Expression (Rank by Sub 3)

  15. Press the “Enter” key. We will have a new calculated column.

    Power BI RANKX - New Calculated Column (Rank by Sub3)

  16. For the existing table, only insert this newly calculated rank column, “Rank by Sub3,” in the “Values” field.

    Power BI RANKX - Add New Field in Values

  17. Now, we can see the table with the old and new rankings.

    Power BI RANKX - Old & New Ranking

The first student, “Janet Martin,” was ranked as 1 based on the total score column but based on “Sub 3,” he is ranked as 4.

Note: We have done so much formatting to this table. You can download the Power BI RANKX template from the link below and apply each formatting technique as applied.

You can download this Power BI RANKX Template here – Power BI RANKX Template

Things to Remember

  • We can skip optional arguments by entering a comma.
  • If we want different ranking techniques in case of a tie, we must use the “TIE” argument of the function.
  • The first two arguments are mandatory, and the rest are optional.

This article has been a guide to Power BI RANKX. Here, we learn how to use the Power BI RANKX function to get the rank based on particular column numbers with the help of an example. You can learn more about Power BI from the following articles: –