# RANKX Considerations – Power BI And DAX Formula Concepts

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 RANKX DAX 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 Course Modules*****
DAX Formula Deep Dives
Mastering DAX Calculations

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Matrix Visualization In Power BI

I will show you formatting tricks on how to put think borders on matrix visualizations in Power BI....

## Power Query Each Expression: An Introduction

Power Query is a data transformation and manipulation tool that's available in Microsoft Excel and...

## VertiPaq Analyzer In DAX Studio | Power BI Tutorial

This tutorial will showcase the VertiPaq Analyzer Metrics in DAX Studio and how it helps in optimizing...

## Convert The Date Table Function Into A Table Query In Power BI

I'm going to show you how to turn an M code for a date table into a table query. You may watch the full...

## Creating Power BI Tables By Using UNION & ROW Function

I'm going to show you how you can create Power BI tables using a formula that combines the UNION...

## Time Comparisons In Power BI: This Year vs Last Year

I want to go over how you can easily do time comparisons in Power BI and specifically calculate this...

## Power BI Slicers Tutorial: Counting Selections Correctly

In today's tutorial, I'm going to work through a subtle issue with Power BI slicers that can trip you...

## Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

## Tips For A Successful Power BI Implementation

We'll continue our series on Power BI project planning and implementation. This time around, we'll be...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Predicting When Will Your Customers Purchase Next w/Power BI

What if you could know when your customers are likely to make their next purchase using predictive...

## Power BI Challenge 14 – Emergency Services Analytics

We've been very busy here at Enterprise DNA as we continue to level up our content so that all of you...