Find Your Top Clients Across Different Measures in Power BI

Here at Enterprise DNA, we are building an analytical movement with Power BI. Today, I am going to walk you through how you can create an awesome visualization where we dynamically showcase only our top ranked clients using multiple measures in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

What we have here is a slicer which dynamically changes the visualizations and measures whenever I make a selection. Let’s say we wanted to look at just the Top 5 customers. We can look at the Top 5 customers by sales, profits, and margins. What’s also cool is that we can link into our data model and then jump into any time frame.

This technique can be reused; you can get any type of ranking by using the exact same technique. I’m going to show you the steps I used to create multiple measures in Power BI and hopefully by the end of it, you’ll be able to see how you can apply this great visualization technique on top of your own data sets and your own environment.

Table of Contents

Creating The Slicer

The first thing I did was to create the slicer. I created a table and called it Ranking Selections and used the Enter Data feature. I listed down my selections: Top 5, Top 20, Top 50, and Top 200 and then put in the Rank Number. We needed a number here not only to sort our selection but also to capture it inside a measure and integrate it into our logic.

Ranking Select Formula

The second step is to create a formula which would enable me to register what ranking is selected. This way, whenever I select different values inside the slicer, it would change to the ranking number. I used a very common technique used to extract values from a slicer: the IF HASONEVALUE.

If the ranking selection has one value, then I want it to equal to the rank number which I get through the VALUES function. If nothing is selected, then I just put in some large number to cover every single result inside the client table. This means that if nothing was selected, then you would get a list of every single client and their performance.

I already have my Core Measures set up like my Total Sales, Total Profits, and Total Costs. I also integrated or branched into my Profit Margins using this measure:

Total Sales By Rank

The next step is to create some logic which ranks our customers dynamically across all of these different metrics. We want to be able to make a selection and see the sales, profits, and margins of our top customers.

They’re all potentially going to be different, right? They’re not all going to be the same customer so we need a dynamic calculation that we can use across all these different measures.

I’ll create a new measure to write out the logic and use a technique that simplifies the code. I’m really big on VARIABLES these days and use them as much as possible. I will call the new measure Top Sales by Rank.

Ranking Dimension

I’m going to use a VARIABLE and call it Ranking Dimension. All I need is a table function which is the dimension that I’m going to rank. This is going to be our customers because we’re trying to find our top clients using all these different measures.

Then I’m going to add the Ranking Selection which will grab the number of how many clients we want to see inside inside these visualizations. Then I will type RETURN here because this is what you need to do to round off a formula when using variables.

The next step is to write down the actual logic. We will calculate Total Sales but only calculate it for the top N customers or whatever number of customers we select. To do that, I am going with the FILTER function and put in the Ranking Dimension.

Writing The Logic

Then I jump down to a new line and put the FILTER on the Ranking Dimension. I will use the RANKX logic but I won’t be able to use the variable inside of RANKX because this variable is not going to be dynamic inside of this context. So I’m just going with Total Sales here in descending order.

Here’s where the dynamic part comes in: I’m going to go with less than or equal to and use our variable Ranking Select. This is only going to return a value if the rank of the Total Sales is below top 5, top 20, top 50, or top 200.

Let’s drag the Customer Name at the table. Normally, it will show every single customer but as soon as I use the logic we just created, it becomes a lot more dynamic. For example, when I select Top 5 on the slicer, it will show my top 5 customers; if I click on Top 20, it will show my 20 customers. Pretty cool, right?

The formula we used basically cuts off anything which is not inside the ranking selection. It says if the rank is less than or equal to the Ranking Selection, then calculate the Total Sales; if it is not, then don’t include it. That’s how we get the dynamic calculation and visualization.

Let us change this into a visual and improve it a little bit by adding data labels and changing the color. So now we the Total Sales of our top clients.

Total Profits By Rank

What’s great is that this pattern can be reused in so many ways. For example, it is easy to jump from Total Sales and then calculate Profits. All we have to do is copy the measure and paste it into our new one and then change Total Sales to Total Profits.

Total Profit Margins By Rank

We do the same thing to get the profit margins of our top customers.

By the end of this analysis, we get this incredible insight using different measures in Power BI where we can not only look at our sales by rank, we can also look at profits by rank and profit margins by rank. Obviously, we want to make sure that these are formatted correctly so we’ll just put them into a percentage format, and then change the colors to jazz it up a bit.

By applying multiple measures in Power BI, we now have three dynamic charts just like that. We can showcase our Top 5, Top 20, Top 50, or Top 200 customers and jump between any time frame.

Conclusion

There are so many applications that you can use with this technique. Hopefully, you can utilize this in your own environments. If you are a really large company and at the end of every month, you want to run a promotion or reward your top customers, this is a quick way to evaluate that grouping of people. Obviously, your marketing can be dictated towards the ranking of your customers.

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