**RANKX is a very unique DAX function that requires a deeper level of thinking** around its logic and in how it actually works when running a calculation in a particular context (for example a table or chart inside Power BI). **You may watch the full video of this tutorial at the bottom of this blog.**

What you don’t want with

RANKXDAX function is to start receiving results and not really understand how they are calculated because you could invariably have incorrect calculations and results that are appearing in your reports.

Each different parameter within the **RANKX** function can change the way in which it calculates a result. There are **multiple parameters that you can input into this DAX function** to understand how each one can determine the underlying result, which is deemed important.

That is why understanding the key elements and concepts of how **RANKX** works in a variety of contexts is essential. This will enable you to be sure that your calculations are correct and that your consumers are viewing insights that actually makes sense.

**In this tutorial, I’ll be discussing how this particular formula and function can be used. **

## Sample Basic RANKX Scenario

Here’s a sample scenario for a simple **RANKX** calculation. First, let’s have a look at the table from the left.

What we’re trying to do here is to **identify the ranking of these products based on** **Sales **(this could also be based on any measure) across all the different products that we sell.

## Using The ALL Function Within RANKX DAX Calculation

Now, let’s take a look at the current formula for this particular scenario.

Primarily, we need to create a table inside the **RANKX** **DAX** function to evaluate every single product based on the measure that we specified.

The **ALL **function was used here to remove any filter from the **Products** table. This is because we need to evaluate every single product and see where the **Total Sales** for each particular product ranks based on all the products.

But, if we remove the filter (in this case, the **ALL **function), and just use “**Products**”, it will show a different result.

As you can see, **it just evaluated to 1**. This is because it is only looking at the current product in the current context (in this case,** Product 63**). Consequently, everything will be ranked **1st** because this is literally the only product in the table that **RANKX **is evaluating over. And that’s mainly why we need to use the **ALL** function to get the correct result.

Now, that is how you can solve simple **RANKX** calculations.

But, what if it gets more complex? What if you’ll need **ranking results that are not just based on Total Sales**?

Well, that’s where we need to understand the current context of this calculation. We need to know how we can manipulate the context to get the result that we want.

## Sample Complex RANKX Scenario

Let’s try to bring in the exact similar formula (**Product Ranking** measure) into the table on the right side.

As you can see, it has given us another erroneous result. The reason behind this is because we also have a **Customer Name **column here.

If we look at the formula again, you’ll see that it is **not breaking out each individual customer**. We are only evaluating the products for each specific customer.

Let’s try to sort out the **Customer Name** column.

Even if we sort out the **Customer Name column**, the **Product Ranking** will still change **based on the ranking of the product that they bought**.

As you can see here, there are lots of numbers that are the same. One example is rank **17**. In this case, it may be because **this particular customer has only bought 17 products**.

Now, that’s maybe what you need in this scenario. But what if you’ll be needing something a bit more complex than that? What if you’ll need **the rank from 1 to an indefinite value of the Customer Name and Product contexts**?

As I’ve emphasized, it is important to **understand the context and how we manipulate the context **to get the result we want.

## Using The CROSSJOIN Function

Currently, we have two dimensions which are from two totally different tables. When you switch to **Modeling View**, you’ll see that the **Customer Name **column** **is coming from the** Customers** table and the **Product Name** column is coming from the **Products** table.

So, we need to somehow **remove filters** on this virtual table inside the **RANKX **logic from both the two tables.

We need to do what we did in the first table (left side), but we need to do it across both the **Customer Name** and **Product Name** columns in both particular tables.

Then eventually, we’ll get the correct ranking via **Total Sales** of **Customer Name** and **Product Name**. Here is the formula that we can use for this distinct scenario.

This **CROSSJOIN** function allows us to join two columns together, or it could also be two tables. However, in this case, we’ll just join columns.

Going further into details, what we’re trying to do in this part is just mainly **removing all the filters **from both **Customers** and **Products** tables.

Then, we’ll be iterating through every single row in this virtual table and evaluating or identifying where the particular rank for this particular **Customer Name** and **Product Name** subset is.

If you check out the results, you’ll see here we’ve finally got the accurate ranking. And also, if you noticed, there are **two rows in the 4th rank**. This is because they both have exactly the same **Total Sales**.

And that is how we achieved the results that we want to attain in this particular scenario.

## Conclusion

Now, those are just some of the considerations when using the **RANKX ** function in **DAX** calculations that we want to cover in this tutorial. There are so many ways that you can do it in so many different functions. But the key takeaway here is that you really need to understand the context where your **RANKX **function is being placed into.

This is definitely the best way to demonstrate **RANKX**, especially when it comes to its versatility in calculating any ranking type analysis. You’ll be amazed at how flexible the RANKX function can be inside Power BI.

It can be used in a number of different scenarios. Plus, it enables some advanced insights which will ultimately impress the consumers of your reports and visualizations.

Good luck reviewing this one.

Sam

******* Related Links*********Using Dynamic Visuals On Ranking Based Parameters In Power BI****Find Top Customers Using RANKX In Power BI****Master Virtual Tables in Power BI Using DAX**

******* Related Course Modules*********DAX Formula Deep Dives****Advanced DAX Combinations****Mastering DAX Calculations**

******* Related Support Forum Posts*********Problem With Ranking (RankX), Showing all Customers****RANKX Vs TOPN What Is The Difference?****Dynamic RANKX With Other Category****For more RANKX support queries to review see here…..**

## 9 comments on “RANKX Considerations – Power BI And DAX Formula Concepts”