Unique Ranking Insights in Power BI Using RANKX & TOPN

[EnterpriseDNA]

When it comes to ranking insights in Power BI, we use the RANKX and TOPN functions. However, these two DAX functions still have their own uniqueness. You can’t just select and use any of the two in any scenario you want. You need to understand how RANKX and TOPN work in order to know when and how to use them.  

We know that both functions do some ranking for us, so we need to understand their difference and that’s what I want to showcase in this blog. I’ll show you some of my tutorials around RANKX and TOPN to give you a substantial demonstration on when and how to use each function. Keep in mind that TOPN is a table function while RANKX returns a scalar value. The key here is to understand the context that you put these functions into.  

So, let’s get to it, starting with RANKX. This first video tutorial shows how a simple RANKX formula does the ranking in a context. 

[video]

Understanding this concept is crucial because we need to make sure that you have correct calculations in our reports. We might have ranking results from a RANKX formula, but we don’t really understand how they were calculated. We could have incorrect results in our reports without knowing it. 

This is just a simple example of utilizing RANKX, but I want to go deeper into how to use RANKX in combination with other DAX functions within a formula. And that’s what this next video is about. 

[video]

RANKX can be quite confusing especially when we have to layer one context on top of another. So, in this demonstration, I used RANKX with CALCULATE, ALLSELECTED, and VALUES to achieve the ranking insight that I wanted to extract from my analysis. 

Here’s another example on how we can implement RANKX in our calculations. This tutorial is about creating dynamic ranking tables, where I used RANKX as a filtering function within a calculation. 

[video]

I have a lot more tutorials around RANKX so make sure you check that out at Enterprise DNA. Now that we have a substantial background of the RANKX function, we’ll now explore TOPN and see the difference between the two. 

From its name, the TOPN function retrieves the top 2, top 5, 10, 20, etc. of a certain context. While RANKX uses particularly the ALL function to rank a context (products, customers, etc.), TOPN returns a given number (1, 2, 5, 10, etc.) that we specify within the calculation. 

Watch this tutorial video and see how I used TOPN to discover my top products per region. Here I wanted to know my top 2 products, and so I input TOPN 2 in the formula.  

[video]

This next example is quite the same, but here I wanted to find out my top 3 salespeople per region. 

[video]

Furthermore, TOPN allows us to generate a table based on ranking. Here’s an example of how I did this. 

[video]

As you can see in these demonstrations, there’s a pattern when using the TOPN and the RANKX. However, note that we can also use RANKX to retrieve a certain number in ranking something. For example, in this video, where I wanted to find out my top 10 clients or customers. I used RANKX here instead of TOPN. The only difference is that with TOPN, you simply put the number (such as 10), while with RANKX there’s a certain logic or pattern. 

Check out the video and see how I did it. 

[video]

Key Points 

In this post, I’ve showcased several of my tutorial videos about RANKX and TOPN, hoping that this will give you clarity and a good grasp on when and how to use these two Power BI functions. These functions can be very complex, but they are both powerful in providing valuable ranking insights. The key is to really understand the context that you apply them to. 

RANKX works in a variety of contexts. That’s how flexible it is to use inside of Power BI. You can implement it in different situations and scenarios and get advanced insights from your analysis. Likewise, TOPN is such an incredible function that gives you specific ranking insights. You can also implement it through complex logic in ranking tables. 

Good luck implementing these functions to your own work. All the best!