For example, if we click on Florida, we’ll see that this chart changes and it’s showing how the top 20% are performing in each month. We could also use this formula in other visualizations and use the power of the data model to find and extract even more insight. All you need to do is change up the columns that are referenced in this formula based on the context that you want to calculate. You could do this to any dimension in your data set and test the Pareto Principle using the same technique. Re-Using The Pareto (80/20) Test Technique So that’s how we test the Pareto Rule within the data set. That’s how we get these numbers down here.įurthermore, to get the % of Sales of Top 20% is simply divide Top 20% of customers by Total Sales. And if we look at the list here, they’re all dynamically calculated for every state. So that’s what’s going on behind the scenes. So this is working out 158 in Florida as the top 20%, and if that customer is ranked by the sales in the top 158, then retain that customer and count up the total sales. and we’re working out with this RANKXif that customer is, by the Total Sales, part of the top 20% that we work out in this formula up here, CustomerPercent. So we’re are filtering every customer that we’ve sold in Florida. We want to break out this top 20%, and this is want the last part of the formula does. So we’re counting Total Sales, but we’re counting it in a different context. This function could change the context of the calculation. So we have to make sure that we reference something that was in the Sales table so that when a filter is placed on a state, say Florida, I would be counting the top 20% of the customers who actually bought in Florida. If I don’t use DistinctCount within this table, that’s not going to dynamically filter for each different state because of the way our data model is set up. The thing to note is that I have used the DistinctCountwithin the Sales table. So I’ve used variables ( VAR) and say the CustomerPercent we are looking for is 20%, so in this case I put in 0.2. I’ve actually calculated it here for this table, but I’ve also done it within this formula so that we don’t have to reference another measure. There’s a little bit to it, but I’ll walk you through each element.įirst thing we do is to work out dynamically the total number of customers. Now I’ll show you the formula that sits in behind this visualization. Most of the data sets have more of a reality to them. Now as you can see the 80/20 rule or principle doesn’t apply here because this data set is totally random. Moreover, say in Florida we’ve made $9 M in sales, and we have the answer in terms of our 20%, $3.5 M, I wanted to see what percent is that to our sales. I’ve also set it up so we could click through each state and we could see the Customers, and the Total Sales of our Customers down in this chart on the right hand side. And that gives us a number that we can feed into our formula to work out.Īfter I’ve done that, I needed to feed that number to some ranking logic and say, within each state, how many sales can be attributed to the 20%? What makes up the 20% of our customers? So what I did was multiply 0.2 by our Total Customers. And when we look at the bottom, we see much less here in Rhode Island, which is a smaller market for us.

We look at, say Florida here, we can see that we sell to 792 customers. So if we’re looking at our states here, I wanted to look at within each state, how much of our Total Sales come from our top 20% of our customers.įirst, I work out how many customers we have in that particular region.

I’m going to show you how I set up this sample dashboard, which is pretty simple. Testing The Pareto (80/20) Principle In Power BI It may not always be the case, but generally it is. If you discover that 20% of your customers make up 80% of your revenues, then you should be spending the bulk of your time trying to extract insights as much as possible from those customers or products, or locations, etc. Because at the end of the day, you want to focus in where you’re generating your best returns in your business. I’m going to show you how you could use a pattern inside Power BI using a formula to work out and test this principle. The list goes on in terms of how this type of analysis can point you in the strategic direction that you should be focusing on from a business perspective. It’s a really good idea to run a test using the technique I showcase in this tutorial, because if it does hold true, then that is where you should be focusing your efforts – from a marketing perspective, from an inventory management perspective. Significant Implication Of The Pareto Principle

Re-Using The Pareto (80/20) Test Technique.

Testing The Pareto (80/20) Principle In Power BI.

Significant Implication Of The Pareto Principle.

Pareto principle formula