Power BI Certification Training Course: PwC A ...
- 100k Enrolled Learners
- Weekend/Weekday
- Live Class
Power BI is a strong business intelligence tool that allows users to create advanced visualizations and perform heavy calculations with DAX or Data Analysis Expressions. RANKX in Power BI is one of the very commonly used DAX functions that enables users to rank values based on a certain column or measure. Thus, sales amounts, customer performances, or product profitability all come under comparative analysis with RANKX.
This blog examines the RANKX function closely, covering its syntax, use cases, and best practices. Microsoft official documentation (source) and the DAX Guide (source) are referenced online to provide accurate and authoritative insight.
Power BI’s RANKX function is crucial to rank the dataset values Top # N on any given field using a set expression. When ranking sales, performance, or any other number across a dataset, it is helpful. The function assigns the ranks based on the proper sorting order after evaluating the expression for every row in a table.
Now you know why Power BI makes use of the RANKX. Let’s examine what RANKX in Power BI is.
RANKX is a very helpful tool for comparative analysis because it provides relative ranks based on a measure or a column. In contrast to static ranking, RANKX displays real-time insights by automatically updating the rank when a filter is applied or even when a slicer is positioned within the image.
RANKX(<Table>, <Expression> [, <Value> [, <Order> [, <Ties>]]])
Next, we’ll look at Power BI Examples of RANKX Use.
To rank salespersons based on Total Sales, use:
Sales Rank = RANKX(ALL(Sales[Salesperson]), <a rel="noopener" href="https://www.youtube.com/watch?v=Bjni4ujgRfQ&t=304s" target="_blank">SUM</a>(Sales[TotalSales]),, DESC, DENSE)
ALL(Sales[Salesperson])
to remove filters and rank globally.DENSE
ensures consecutive ranking without skipping numbers for ties.To rank products within each category:
Product Rank in Category = RANKX( FILTER(ALL(Sales), Sales[Category] = SELECTEDVALUE(Sales[Category])), SUM(Sales[TotalSales]), , DESC, DENSE )
This ensures ranking is category-specific instead of global.
Let’s examine how to handle ties in RANKX.
SKIP (Default): Duplicates rank by the same number, then skips next.
DENSE: Duplicates are ranked by the same number, then do not skip next.
For example:
Values - 100, 90, 90, 80.
SKIP: Ranks 1, 2, 2, 4.
DENSE: Ranks 1, 2, 2, 3.
This also allows you to do the DENSE option, which gives you continuous ranking with no gaps.
The next example we’ll examine is the retail store performance ranking.
A Retail Manager ranks stores by revenue within each region to identify top and low performers.
StoreID | StoreName | Region | TotalSales |
1 | Store A | East | 500,000 |
2 | Store B | East | 450,000 |
3 | Store C | West | 700,000 |
4 | Store D | West | 680,000 |
5 | Store E | East | 480,000 |
Store Rank in Region = RANKX( FILTER(ALL(StoreSales), StoreSales[Region] = SELECTEDVALUE(StoreSales[Region])), SUM(StoreSales[TotalSales]), , DESC, DENSE )
StoreName | Region | TotalSales | Rank in Region |
Store A | East | 500,000 | 1 |
Store E | East | 480,000 | 2 |
Store B | East | 450,000 | 3 |
Store C | West | 700,000 | 1 |
Store D | West | 680,000 | 2 |
This ranking helps the manager strategize sales improvements.
Following the Real-World Example of Retail Store Performance Ranking, we will now discuss Best Practices.
Avoid filtering inside RANKX for large datasets. Instead, use SUMMARIZE to pre-aggregate data.
If rankings change unexpectedly with slicers, use ALLSELECTED instead of ALL:
Rank with Filter Context = RANKX(ALLSELECTED(Sales[Salesperson]), SUM(Sales[TotalSales]))
Exclude null or zero values unless needed:
Sales Rank = IF( SUM(Sales[TotalSales]) > 0, RANKX(ALL(Sales[Salesperson]), SUM(Sales[TotalSales])) )
RANKX in Power BI is a powerful DAX function for ranking values dynamically. It works best in measures, ensuring efficient performance, especially in large datasets. Proper use of filters and context enhances accuracy, while combining it with slicers and conditional formatting improves report interactivity.
The blog discusses the RANKX function in Power BI and highlights its ability to dynamically rank values based on a given measure or column. It provides a flexible approach to ranking while allowing users to control tie-breaking methods and filtering contexts. By replacing manual ranking calculations, RANKX improves performance and ensures consistency across reports. When used effectively, it enhances the clarity of data insights and makes Power BI dashboards more interactive and insightful
If you’re looking to advance your Power BI skills and career opportunities, consider enrolling in the Power BI Course: PwC Academy by Edureka. This program, designed in collaboration with PwC, provides dual certification in Business Intelligence and prepares you for the PL-300 certification exam. With live instructor-led sessions, hands-on real-world projects, and simulated business scenarios, this training ensures you gain practical expertise in Power BI
Do you have any questions or need further information? Feel free to leave a comment below, and we’ll respond as soon as possible!
edureka.co