Rankx is a type of function in power bi and it is an inbuilt function which is termed as a sorting function which means it 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
RANKX function in Power BI works similar to the one in excel RANK function and assigns rank based on numbers of the specific or mentioned column. Below is the syntax of RANKX DAX function in Power BI.
Table: We need to specify based on which table you are ranking.
Expression: Based on which column you are ranking, we need to supply the column name here.
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 order or Descending order. The default parameter is in Descending order i.e. top value will be ranked as 1 and so on.
We can supply two arguments here TRUE or FALSE. TRUE is for ascending order i.e. the lowest value ranked as 1 and if you supply FALSE as then it will rank in descending order i.e. highest value ranked as 1.
Ties: This is the important thing you need to learn. In this argument, we can specify what should be done in case there is a TIE between two values.
- If you SKIP this the rank after the tied value will be the rank of tied value plus a count of tied values. 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 then next value after the tied rank will be then next rank of tied values rank. For example, if there are 3 values tied at 5th rank then the next rank will be 6 = (5+1).
Example of RANKX Function in Power BI
Below is an example of a RANKX Function and to apply this function in Power BI I have created below data in Excel.
This is the data of marks obtained from 6 subjects. Based on different parameters we will rank these students.
Copy and paste the data directly to Power BI or you can copy the data to excel file and then import to Power BI as Excel file reference. So you can download the excel workbook template from the below link which is used for this example.
I have directly uploaded the data to Power BI.
- Go to Report View.
- In the field section right-click on the table name and choose “New Column”.
- First, we need to enter what should be the column name, I will name it as “Rank By Total Score”.
- For this new column open Power BI RANKX function.
- Table is the first parameter of this function, so our table name is “Table2” supply the same.
- Expression is nothing but based on which column value we need to rank, in this case, based on “Total” we are ranking, so will supply the same.
- Close the bracket and Hit enter key to get this new calculated column in the table.
- Now insert blank “Table” visual to see how our RANKX formula works.
- Drag and drop Student Name, Total and newly calculated column i.e. Rank By Total Score to “Values” field of “Table” visual.
- This will give us each student’s RANK against the total score.
So, we have a ranking based on the scores of each student.
- By using the RANK column we can sort the data. Place a cursor on a rank column to see a small down arrow key.
- Now just in case assume you want to rank students based on “Sub3” scores, for this insert one more column.
- Once again open Power BI RANKX function and supply the TABLE name.
- For Expression instead of giving the “Total” column supply “Sub 3” column.
- Hit enter key, we will have a new calculated column.
- Now for the existing table only insert this newly calculated rank column i.e Rank by Sub3 in the “Values” field.
- Now we can see the table with the old and new ranking.
Ok, the first student “Janet Martin” was ranked as 1 based on total score column but based on “Sub 3” he is ranked as 4.
Note: I have done so much of formatting to this table, you can download the Power BI RANKX Template from below link and apply each formatting technique as applied.
Things to Remember
- Optional arguments can be skipped by entering a comma.
- If you want different ranking techniques in case of tie use the “TIE” argument of the function.
- The first two arguments are mandatory and rest are optional.
Recommended Articles
This has been a guide to Power BI RANKX. Here we learn how to use 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 –
