How to Use RANKX in Power BI

Ranking your data, whether it’s top-performing products, sales reps, or marketing campaigns, is one of the most fundamental tasks in data analysis. In Power BI, DAX provides a powerful function specifically for this job: RANKX. This article will walk you through what RANKX is, how its syntax works, and how to apply it in common business scenarios, from simple rankings to more complex, conditional ones.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is RANKX in Power BI?

RANKX is a DAX function designed to return the ranking of a number in a list of numbers for each row in a given table. What makes it powerful - and sometimes tricky - is that it's an iterator function. This means it doesn't just look at a final value, it goes through a specified table row by row, evaluates an expression for each row, and then determines the rank of the current row's result against all the other results.

Think of it like a manual process. If you wanted to rank salespeople by revenue, you would first need a complete list of all salespeople and their total revenue. Then, you'd go down that list one by one, comparing each person’s revenue to everyone else's to decide their rank. RANKX automates this entire process inside a single DAX measure or calculated column.

Understanding this row-by-row evaluation (also known as "row context") is the key to mastering RANKX and troubleshooting common issues, like when all your items mysteriously get ranked as number 1.

Understanding the RANKX Syntax

Before writing our first formula, let's break down the components of the RANKX function. At first glance, it looks a bit intimidating, but each part has a specific and logical job.

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

• <table> (Required): This defines the list of items you want to rank against. It tells RANKX which set of rows to loop through. You might think you can just put your primary data table here (like 'Sales'), but this is where people often get stuck. To rank across all items regardless of the filters in your visual, you typically use functions like ALL() or ALLSELECTED() to create the correct list.
• <expression> (Required): This is the calculation you want to rank. It's the metric that determines the ranking order. This is usually another measure you’ve already created, like SUM(Sales[Revenue]) or a more complex one like [Profit Margin].
• [<value>] (Optional): This parameter is rarely used. By default, RANKX ranks the result of the <expression>. The <value> parameter is for advanced scenarios where you want to rank a specific, fixed value within the list generated by the expression. For 99% of use cases, you can just leave it blank.
• [<order>] (Optional): This determines if a higher value is better or worse for ranking purposes.
• [<ties>] (Optional): This tells the function how to handle two or more items having the exact same value in the expression.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Your First Simple Rank: Ranking Products by Sales

Theory is great, but let's build something practical. Let's create a measure to rank all products by their total sales revenue.

Imagine you have a Sales table with Product Name and Revenue columns. And you've already created a basic measure called [Total Sales]:

Total Sales = SUM(Sales[Revenue])

Step 1: Create a New Measure for the Rank

In the Power BI ribbon, click "New Measure." This will open the formula bar where you can type your DAX expression.

Step 2: Write the RANKX Formula

Now, let’s write the DAX to rank products. We want to rank against all products, so we'll use the ALL() function for our table parameter. This function removes any existing filters from the specified table or columns, ensuring we are always comparing a product against the complete list of products.

Product Rank by Sales = 
RANKX(
    ALL('Sales'[Product Name]),
    [Total Sales],
    , // Skip the optional 'value' parameter
    DESC,
    DENSE
)

Let's quickly break this down:

• ALL('Sales'[Product Name]): This is our ranking table. It tells RANKX to create a list of all unique product names in the sales table, ignoring any filters that might be applied in a chart or slicer.
• [Total Sales]: This is the expression we're using to determine the rank - our core metric.
• DESC: We specify descending order because a higher sales amount should get a better rank (i.e., Rank #1).
• DENSE: We're using dense ranking to prevent skipping numbers if any products have the same sales total.

Step 3: Add the Measure to a Table Visual

Drag the Product Name column onto your report canvas to create a table. Then, add your [Total Sales] measure and your newly created [Product Rank by Sales] measure. Finally, sort the table by the rank measure to see the results in order. Voila! You now have a dynamic ranking of your products.

Without using ALL(), your measure would evaluate in the context of only the current product in the table visual. It would be asking, "How does this one product rank against a list containing only itself?" The answer would always be #1, which is the most common reason people get stuck.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Taking it Further: Dynamic Ranking Within Categories

A global ranking is useful, but what if you want to rank products within their respective categories? For example, what is the best-selling "Snack" and what is the best-selling "Beverage"? This requires a slightly more nuanced approach.

If you were to add a Category column to the table we just built, the simple Product Rank by Sales measure would break. This is because ALL('Sales'[Product Name]) still gets every product in the entire Sales table, so it continues ranking products globally instead of respecting the category groups.

To fix this, we need to tell RANKX to respect the filter context for the category but ignore it for the product. The perfect function for this job is ALLEXCEPT().

Here's the refined DAX formula:

Product Rank within Category = 
RANKX(
    ALLEXCEPT(
        'Sales',
        'Sales'[Category]
    ),
    [Total Sales],
    ,
    DESC,
    DENSE
)

How it Works:

The ALLEXCEPT('Sales', 'Sales'[Category]) part is the magic here. It tells DAX: "Give me the Sales table with all filters removed, except for any filters on the Category column."

When you use this in a table visual that includes categories, the measure will grab the current category (e.g., "Snacks"), generate a list of all products just within that category, and then rank them against each other. It repeats this process for every category in your visual, effectively "restarting" the count for each group.

Common RANKX Pitfalls and How to Avoid Them

RANKX is powerful, but it has a few famous quirks. Here are the most common problems you'll encounter and how to solve them.

1. Everything is Ranked #1

This is by far the most common problem. If you get a column of all 1s, it's almost certainly an issue with filter context. Your <table> parameter isn't providing the full list of items to rank against. In the context of a table visual, the measure is evaluating for each line, a list that contains only one single item - that line's product. The fix is to use a function like ALL(), ALLSELECTED(), or ALLEXCEPT() in your first argument to create the correct scope for the ranking calculation.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Poor Performance on Large Datasets

Because RANKX is an iterator, it can be computationally expensive on very large models with millions of rows. If your report becomes slow after adding a RANKX measure, consider these options:

• Calculated Column vs. Measure: If your ranks don't need to change dynamically with slicers (e.g., you just need a static, all-time ranking), consider creating the rank as a calculated column instead of a measure. The calculation is done once during data refresh and stored in the model, making it much faster at query time. The downside is that it won't react to user selections or filters.
• Optimize Your DAX: Using a more targeted table function can help. For instance, using ALL('Products'[Product Name]) instead of ALL('Products') is slightly more efficient because it creates a list from just one column instead of the entire table.

3. Blanks Interfering with Ranks

Sometimes, your expression might result in a blank value for certain rows. By default, DAX treats blanks as 0 when ranking, which can place them at the top or bottom of your ranked lists when you don't want them there. You can handle this by wrapping your expression in an IF statement to either return a value that puts them at the end (like a very large negative number if ranking descending) or to filter them out entirely within a CALCULATE statement.

Final Thoughts

Wrapping your head around RANKX is a major milestone in mastering DAX. It combines two core concepts: iterator functions and filter context. Once you understand that its first parameter needs a complete table to evaluate, you can solve most common issues by using functions like ALL and ALLEXCEPT to shape that table correctly.

The learning curve for DAX can be steep, preventing entire teams from making data-driven decisions. Hours spent watching tutorials on tools like Power BI are better spent acting on insights. We designed Graphed to remove this technical barrier. By connecting your sources, you can ask questions in plain English - like "Show me my top 10 salespeople in the UK for last quarter" - and instantly get a real-time dashboard without writing a single line of code.