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
[youtube https://www.youtube.com/watch?v=W5lzIJ4qY7M?rel=0&w=784&h=441]
***** 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…..
