Automatically Find Your Top 3 Salespeople Per Region Using DAX In Power BI

There’s so much you can do with DAX in Power BI, and one of these things is for you to be able to find your top 3 salespeople for each region automatically. You may watch the full video of this tutorial at the bottom of this blog.

Picture this too familiar scenario….

You are in a meeting and asked about your sales territories. Who are your top three sales people in each territory? Drilling deeper, what are they selling, when are they selling it and how much?

What if there was a dynamic way to uncover such insights that historically would have been time consuming to find and difficult to drill down into, making you look super professional and organised in the aforementioned meeting situation?

By utilising DAX in Power BI, we can in fact dynamically uncover these insights and easily visualize information of this sort.

In this tutorial, I’ll explain how to construct the key formula needed to achieve this and why this information might be valuable.

This technique could be used for a range of different applications.

Table of Contents

How To Find Your Top 3 Salespeople

Before we dive into the formula, let’s create a table of our regions.

We jump to the Locations table and grab the Name column. Then, add the Salespeople as well.

We’re also going to utilise the Data Bars column. It is just an awesome visualization released by Power BI.

So for instance here, for every single region and for every single salesperson, we can actually get the Total Sales.

We could then rank it by selecting something in the chart and see how things are performed over time.

We could also bring in our Dates context here and turn it into a slicer. And when we change the context and the time frame, we see how that change the table.

But we want to somehow automate this or retrieve our top 3 automatically, and dynamically see those Top 3 for any context that we put it into.

And with this, you have to sort through every single sales person and see who ranked first, who ranked last, so on and so forth.

But, we only want the Top 3 and we want to do it really efficiently. We have to implement a formula that will enable us to do that.

Working Out The Formula

We go to New Measure, and we’ll call it Top 3 Salespeople per Region. We will use variables here because it’s just the best way to compartmentalize formulas that we write.

So we go VAR, and then on the next line, we’ll write RankingContext. We will rank the salespeople, so we will create a table using the Values of Salespeople.

Then we write the formula.

Obviously, we put a Return here and go Calculate total sales on the next line because we still want to calculate Total Sales.

We only want to calculate the Top 3 though and whatever context we have in our table. The context here is actually the name of the retail site.

We will use the function TOPN, which is seriously cool. And we go ALL salespeople here, and then Total Sales because TOPN is going to rank by Total Sales.

Then, we’ll do this little trick — we’re going to bring back some context, and that’s what the RankingContext variable is for.

When we push enter and I’ll bring this in, we’ll see now that this only shows three times per region.

You’ll see that it actually brings in the same result, but it actually blanks out, people that aren’t the Top 3.

Formula Explained

Here we see that for every single region, we have the Top 3 salespeople.

We can change the time frame and see that it continuously and dynamically sorts and calculates only for the Top 3.

The thing to be recognized with TOPN is that, with where it’s being placed inside of Calculate, it is the filter.

So what we’re saying here with this function TOPN is that we rank the Top 3, to only return a table or filter off the Top 3 Salespeople based on Sales.

And because of the ALL, this actually takes out the salespeople context. So if we didn’t have ALL here, we would get the same results for every single salesperson.

We need to bring back that context within the calculation and then implement it inside our table.

There’s a lot of things I demonstrate through the Enterprise DNA TV channel, that it’s a pattern.

So you could actually implement this across a range of different scenarios.

Perhaps you want to look at your top 3 Product Sales, or your top 3 Customers, etc.

It doesn’t have to be against the Name context. It could be against the Product context or any of the dimensions you have in your data model.

So if we get rid of our Total Sales and just change this around, because everything outside the Top 3 is blank, then we only return the Top 3 here now. So everything is showing dynamically only for the Top 3.

We can then use the amazing Data Bars. We simply click the drop down arrow at the side and go Conditional Formatting and click on Data Bars.

All we have to do is create some colors here, and now we have these awesome table visualization.

We can get some really good insights here and utilise them to do recognition and rewards efficiently.

To finish it off, we’re going to create the dashboard. If we want to drill into a specific state, we could just change it into a visualization.

With this dashboard, we could drill into any region and we can see every single retail site that we have. We could even select one and see who’s our top 3 people.

Conclusion

There are so many implications that you could utilize this calculation.

There are so many applications here. Instead of having Totals Sales, you could have Profit Margins, or Transactions, etc.

In this example, the analysis could be used to recognize and reward the top three salespeople in each region on a monthly basis without the need for complex and time-consuming data-collection and analysis, which needed to started again as soon as the prior month’s analysis was completed.

Some other uses could include, highlighting your top three customers, your top three products, or the three largest cost groups per region.

Ultimately, these insights can help you to hone your business strategy around products or regions.