WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI RANKX Function

Power BI RANKX Function

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 similarly to the one in excel RANK function and assigns rank based on numbers of the specific or mentioned column. Below is the syntax of the RANKX Power BI DAX function.

RANKX Function

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., the top value will be ranked as 1 and so on.

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course

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., the 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 the 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 the below data in Excel.

Power BI RANKX (Excel Data)

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 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 below link, which is used for this example.

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

I have directly uploaded the data to Power BI.

Power BI RANKX - Import Data

  • Go to Report View.

Power BI RANKX - Report View

  • In the field section, right-click on the table name and choose “New Column.”

Power BI RANKX - Add New Column

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

Power BI RANKX - Rename Column

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

Power BI RANKX - Rankx Function

  • The table is the first parameter of this function, so our table name is “Table2” supply the same.

Power BI RANKX - Enter Table Name

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

Power BI RANKX - Expression

  • Close the bracket and hit the enter key to get this new calculated column in the table.

Power BI RANKX - New Calculated Column

  • Now insert blank “Table” visual to see how our RANKX formula works.

Power BI RANKX - Insert Table From Visual

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

Power BI RANKX - Enter Data in Values Field

  • This 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.

  • 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

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

New Column

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

Enter Table Name (To Rank by Sub3)

  • For Expression instead of giving the “Total” column supply “Sub 3” column.

Expression (Rank by Sub 3)

  • Hit the enter key; we will have a new calculated column.

New Calculated Column (Rank by Sub3)

  • Now for the existing table, only insert this newly calculated rank column, i.e., Rank by Sub3 in the “Values” field.

Add New Field in Values

  • Now we can see the table with the old and new ranking.

Old & New Ranking

Ok, 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: I have done so much of formatting to this table, you can download the Power BI RANKX Template from the below link and apply each formatting technique as applied.

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

Things to Remember

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

Recommended Articles

This 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 –

  • How to SUMMARIZE in Power BI?
  • Power BI Maps
  • Reports in Power BI
  • Tutorial in Power BI
  • Power BI Calendar
0 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI RANKX Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI RANKX Excel Template

New Year Offer - Power BI Training Certification (22+ Hours of Training) View More