TOPN is an amazing DAX function to use in Power BI when you want to apply some internal ranking logic to your calculation. We can use TOPN instead of RANKX as the filtering function and create some additional context or additional filters based on a ranking. This makes TOPN a really powerful function that makes complex calculations and extracting complex insights relatively seamless.
In this post, we’re going to explore the TOPN function, and we’ll go over when and how to use it. TOPN has several usages within our reports to get the insights we want to showcase.
I’ll show you in this video below how TOPN is used to rank the sales for only the top 2 products. It’s easy to calculate the total sales of our products per region, but we can have a really busy chart, showing all of our product sales. What if we only want to focus in on our top products?
I believe this insight is valuable when we’re looking for outliers in our data over time. Perhaps we can see that some products are selling well in certain regions compared to others. This can be an opportunity to have promotions and upsells.
Here I run through the DAX formula that we need to achieve this insight, highlighting how TOPN is used inside the CALCULATE statement.
The technique used in this tutorial is quite useful for many different scenarios, not just selling products. It could be best-selling regions, best customers, top salespeople, and so on. We could also isolate costs or profit margins using this technique.
In this next video tutorial, you’ll see how I’ve applied TOPN to some unique calculations. I used TOPN with the AVERAGEX and the CALCULATE functions to compare sales versus the top 5 sales average. This example is also a perfect insight into the usage of TOPN.
TOPN enables us to manipulate a virtual table based on a ranking, and then run a calculation based on that virtual table. And that is what this tutorial is about. In this case, we’re placing the TOPN virtual table inside an iterating function, which is AVERAGEX.
Another valuable insight for business where I used TOPN is identifying the best-selling day for every product. This approach enables us to look dynamically into any of our stores, and then analyze the best-selling day for each of our product that our stores sell.
In this tutorial, I run through how we can utilize TOPN with MAXX, which is also an iterating function, to extract this kind of information. We can get some solid data from this insight to ultimately help us sell more of our products.
Check out the video below to learn more about this technique.
With this analysis, we can find out why some products sell better in certain regions on certain days compared to other regions, where they might sell better on another day. This can be our basis for decision making and maybe offer promotions or discounts on those trends in those stores.
There are many occasions where there is a better formula combination to use than the alternative of RANKX, and that is to use TOPN. The big difference between TOPN and RANKX is that TOPN is a table function while RANKX returns a scalar value.
This next video demonstrates how TOPN is used to really drill into the data and get the top and the bottom results. We’re analyzing how much revenue does each of our customers generate in each location. We then rank the results virtually and only calculate the top 2 and the bottom 2.
It is a very complex calculation, but it’s made easy with TOPN. I crafted the formula carefully to ensure that we get this insight correctly. If we use the wrong elements, then it will bring us a different result. It’s easy to get confused and lost when we’re trying to drill deep into our data.
Watch the video below and check out how I came up with the calculation.
These are just some of my examples on how to use the power of the TOPN function inside Power BI. I’m sure there are so many more ways we can utilize this DAX function to create analysis and extract insights in a fly.
Nonetheless, this post is a great tool for you as you progress with Power BI. Feel free to reuse the formula patterns I showcased with any dimension that you could think of that you have in your data model. The tutorial videos here will help you understand better when and how to use TOPN for you to generate valuable insights quickly and easily.