Find Top Customers Using RANKX In Power BI

by Sam McKay, CFA | Business Intelligence, Power BI

Power BI is an amazing analytical engine! In this blog post, I’m going to show you how you can find your top customers – not just as static numbers, but through time and through any time period you could want. This is the power of DAX combined with the data model at its best. I will use DAX functions such as CALCULATE, FILTER and RANKX in Power BI, so there are plenty of tips around how you can use them in this tutorial. I also show how you can combine them all to get interesting calculations and results. You may watch the full video of this tutorial at the bottom of this blog.

What we want to discover is to see who are our top customers not only for a static period of time, but who our top customers are through time. We are going to look at every customer we have for every time period. Then we will showcase it in a matrix sales of our top 10 customers and top 5 customers.

Table of Contents

Using The Matrix Preview

Let’s set things up by grabbing the Customer Names in the canvas. Then we’re going to use this new visual called Matrix Preview. You can turn this feature on by going to ‘Options and settings,’ then ‘Options.’

Once we go to ‘Preview features,’ we turn all of these on by ticking all of the boxes.

We’re going to use this matrix, grab the Quarter & Year and the Total Sales measures and put them into this table. You’ll see that we get all of the results, which is great, but it can be very hard to see any insight and spot trends.

Finding Out The Top 10 Clients

We want to see trends and we want to see who our top ten customers are in this period. For example, did we have a customer who for a period of time was one of our top customers but has totally fallen away and has not appeared in our top 10 customers post? So those are the kinds of insights we’re looking for here.

We can do this all with one formula. We just need to create a new measure and call it Top 10 Clients. We are going to use the CALCULATE function to calculate our Total Sales, but we only want to calculate the Total Sales for the customer who sits within the Top 10.

So we have to use ranking logic to create a ranking logic indicator that will say that if this customer is in the Top 10, then return this Total Sales value. Aside from using RANKX in Power BI, we’re also going to use the FILTER function and then use Values, which is going to create an internal table for every single customer.

Then we need to think about what logic to write here, something that says “if the current rank is less than or equal to 10 equal to total sales if not, equal blank.” What’s really cool about filter statements like this is that you can write this type of logic inside.

So, we are going to use the RANKX function and ALL Customer Names because we are going to rank each customer in any particular context. What ALL does is to get rid of any filter on Customer Names so that we can complete this ranking.

We will then rank by Total Sales and rank in a descending order, and then we will go less than or equal to 10. If it does, we want it to equal to Total Sales and if not, to go blank.

Let us now substitute our Total Sales for our Top 10 Clients. We can now see the Top 10 Clients for every single time period.

Using Conditional Formatting

This is already pretty cool by itself, but still we can do a little bit more here. We can use the conditional format just by clicking the dropdown arrow in the Top 10 Clients section in the values area. We are not going to do anything else and just click ok.

Now, we are dynamically viewing our customers through time and see how they have actually performed.

Obviously, this is a bit more random than what you might generally find in an actual real client data, but what we can do here is we can very quickly see trends. For example, this client called Medline became one of our top customers in 2016 compared to earlier in 2015 when they didn’t even register in the Top 10.

Determining The Top 5 Clients

This is a really cool technique and this pattern of code can be reused in a number of different ways. Let’s say you wanted to look at the Top 5 Customers. All you have to do is change 10 to 5 and now we’re looking at our top five clients for every single time period. We also have the ability to change these time periods.

Remember that this is all dynamic. Everything in this DAX formula that we’ve written is dynamic so if we change the time frame to just the years, we would get just the top five clients in those particular years.

If you are selling to clients plenty of times, you will want to shorten down the timeframe; but if it’s only you selling to customers two or three times a year, then maybe you want to analyze things from a longer-term timeframe.

Conclusion

This type of analysis would be great from a head office perspective. You always want to be really focused on where your revenue will be coming from, so keeping a very close eye on the main contributors to your performance is crucial. Also understanding how this make up of customers changes over time will let you know who to focus your marketing and advertising efforts on, and also provides some attribution around why you’re losing business (as another example).

Aside from using functions like RANKX in Power BI, I also used some conditional formatting techniques inside the great table visualizations. Adding some color can really make your numbers stand out so I highly recommend it. It helps the eye to spot trends that much easier than just looking at bland numbers on a page. Make sure your color palettes work well also.

Good luck with this one.

Leave me a comment if you have any thoughts or feedback on this technique.

[youtube https://www.youtube.com/watch?v=hX-s1htBdmk?rel=0&w=784&h=441]