How to Use RANKX Measure in Power BI

The RANKX function is one of the most powerful tools in your Power BI toolkit, but it can also be one of the most confusing for new and even intermediate users. This guide breaks down exactly how to use RANKX, from its basic syntax to more advanced scenarios, so you can build dynamic ranking reports with confidence.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Is RANKX and Why Use It?

In simple terms, RANKX is a DAX (Data Analysis Expressions) function that returns the ranking of a number in a list of numbers for each row in a specified table. Think of it as your go-to function for creating leaderboards and performance reports.

You can use it to answer essential business questions like:

• Which products are our top 10 best-sellers?
• How do our salespeople rank against each other this quarter based on revenue?
• What are our most viewed blog posts?
• Which marketing campaigns generated the most leads?

Instead of manually sorting tables, RANKX allows you to create a dynamic measure that calculates the rank on the fly, updating automatically as your data changes or as you interact with slicers and filters.

Breaking Down the RANKX Syntax

At first glance, the syntax can look a little intimidating. But once you understand each piece, it starts to make a lot more sense. Let's look at the components one by one.

RANKX(<,table>, <,expression>, [, <,value>,], [, <,order>,], [, <,ties>,])

<,table&gt,

This is the table over which the ranking will be calculated. This is the part that trips up most people. You aren't just putting your main data table here. Instead, you'll typically use a function like ALL() or ALLSELECTED() to tell Power BI the precise context for the ranking.

• ALL(YourTableName) or ALL(YourColumnName): This tells RANKX to ignore any existing filters on the specified table or column, giving you a rank against all items. This is the most common choice.
• ALLSELECTED(YourColumnName): This is slightly different. It tells RANKX to rank against all the items currently visible after slicers have been applied. For example, if a user selects three product categories in a slicer, this will rank items only within those three categories.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

<,expression&gt,

The expression is the value you want to rank by. In most cases, this will be another measure you've already created, like [Total Sales], [Total Leads], or [Average Session Duration]. The RANKX function will evaluate this expression for every row in the <,table&gt, you defined and rank them accordingly.

[<,value&gt,] (Optional)

This optional parameter is rarely used. It's meant for advanced scenarios where you want to rank a specific, fixed value instead of the result of the expression in the current context. For 99% of use cases, you can leave this blank, and RANKX will use the <,expression&gt, automatically.

[<,order&gt,] (Optional)

This determines whether you are ranking from highest to lowest or lowest to highest. You have two choices:

• DESC (Descending): Ranks from highest value to lowest (rank 1 goes to the largest number). This is the default.
• ASC (Ascending): Ranks from lowest value to highest (rank 1 goes to the smallest number). Useful for ranking things like cost or error rates.

[<,ties&gt,] (Optional)

This one is important. It tells Power BI how to handle rows with the same value. Your two choices have a significant impact on how subsequent ranks are numbered:

• SKIP: This is the default. If two items tie for 2nd place, they will both get the rank of 2, and the next item will be ranked 4. The number 3 is skipped. The ranking looks like: 1, 2, 2, 4, 5.
• DENSE: If two items tie for 2nd place, they will both get a rank of 2, and the next item will be ranked 3. No numbers are skipped. The ranking looks like: 1, 2, 2, 3, 4. In most business reports, DENSE provides a more intuitive result.

Creating a Basic RANKX Measure: Step-by-Step

Let's create a practical example: ranking products by their total sales. Imagine you have a Products table and a Sales table with a Revenue column.

Step 1: Create a Base Measure for Sales

Before you can rank, you need something to rank by. Let's create a simple measure for total sales. In the Power BI report view, right-click on your Sales table and select "New measure".

Total Sales = SUM(Sales[Revenue])

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Write the RANKX Measure

Now, let's create the measure that will rank our products. Right-click on your Products table and select "New measure".

Product Rank = RANKX( ALL('Products'[Product Name]), [Total Sales], , DESC, DENSE )

Let’s break that down:

• ALL('Products'[Product Name]): We are telling it to look at the entire list of product names and ignore any filters a table visual might be applying to a single row. This ensures we rank each product against all other products.
• [Total Sales]: We are ranking based on our sales measure.
• The third parameter, value, is left blank.
• DESC: We want the product with the highest sales to be ranked #1.
• DENSE: If two products have the exact same sales, we don't want to skip any rank numbers.

Step 3: Add it to a Table Visual

Now for the payoff. Drag the 'Product Name' column from your Products table onto the canvas to create a table. Then, drag your [Total Sales] and [Product Rank] measures into the table. You should now see a clean list of products along with their sales and their corresponding rank.

Leveling Up: Addressing Common Scenarios

Real-world reporting is often more complex. Here's how to handle a few common challenges.

Scenario 1: Ranking Items Within Categories

What if you want to rank products within their categories? For example, showing the top product for "Electronics" and the top product for "Apparel" separately. The previous measure won't work correctly because ALL() removes all context, including the category context.

The solution is a function called ALLEXCEPT. It removes all filters from a table except for filters on the columns you specify.

Rank Within Category = RANKX( ALLEXCEPT('Products', 'Products'[Category]), [Total Sales], , DESC, DENSE )

In this measure, ALLEXCEPT modifies the ranking context. It says, "Rank all the products, but keep the filter on the product category." When you put 'Category' and 'Product Name' in a table with this measure, the rank will restart for each new category.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Scenario 2: Making Your Rank Work with Slicers

If you used the ALL() function, you might notice that your rankings don’t change when you use a slicer. This is because ALL(), by design, ignores all filters.

To fix this, simply swap ALL() for ALLSELECTED(). ALLSELECTED() respects the active slicer selections on the page.

Dynamic Product Rank = RANKX( ALLSELECTED('Products'[Product Name]), [Total Sales], , DESC, DENSE )

Now, if your user filters the report to only see a few brands, this measure will re-calculate the rank based only on the brands they've selected.

Common RANKX Pitfalls (and How to Fix Them)

If your RANKX measure isn't working as expected, it's likely one of these common issues.

• Everything is Ranked as "1": This is the most famous RANKX problem. It happens when you forget to wrap your table/column in ALL(), ALLSELECTED(), or a similar context-modifying function. Without it, RANKX only sees one row at a time - the current row - and ranks it as 1 out of 1. Always check that first argument!
• Ranks Are Slowing Down My Report: RANKX is an iterator function, meaning it has to scan through the table for every single calculation. On tables with millions of rows, this can be slow. If your data is relatively static, consider pre-calculating the rank in Power Query (M code) before the data even gets to DAX.
• My Zeros Are Getting Ranked: Sometimes, you have items with zero sales or activity that you want to exclude from the ranking. You can handle this with a simple IF statement wrapping your RANKX measure. For example: Product Rank = IF([Total Sales] > 0, RANKX(...)). This will return a blank for any item with no sales.

Final Thoughts

Mastering RANKX is a big step toward becoming proficient in Power BI. By learning how to control its calculation context with functions like ALL, ALLSELECTED, and ALLEXCEPT, you can move beyond simple tables and build sophisticated, dynamic ranking reports that deliver powerful insights for your business.

While mastering DAX functions like RANKX is incredibly valuable, we know it involves a steep learning curve and time commitment that busy teams can't always afford. At Graphed, we focus on removing that complexity entirely. Instead of writing formulas, you can simply ask, "show me my best-selling products by region this quarter," and instantly get an interactive, real-time dashboard. We connect all your data sources and let you build reports with simple, natural language, turning what could be hours of data work into a 30-second conversation. If you're ready to get straight to the insights, give Graphed a try.