Last week, we discussed the concept of narrowing your focus to eliminate unprofitable customers. Today, we take that concept a step further with a tutorial on creating a dynamic and real time customer rationalization tool in PowerBI. By the end of this tutorial, you should possess the capabilities and understanding of how to build a Pareto Analysis using your own data in Power BI.
Before we Begin: A Look at the Pareto Principle
The Pareto Principle is a way to make business decisions utilizing the 80/20 principle of thinking. The 80/20 rule is a way to statistically look at a set of data to determine the impact of one factor on the overall data set. For this example, we can look at the impact of one customer’s revenue and gross margin on the overall portfolio of clients. The 80/20 rule references the idea that 80% of the positive aspects of a metric are coming from 20% of specific factors. So, in this example, the idea would be that 80% of your revenue and gross margin is coming from 20% of your customers. This can be helpful in identifying which of your customers you should be prioritizing, and it is a great way to better train your sales staff in understanding the dynamics of your current customer portfolio.
When looking at gross margin and revenue of your customers, we can leverage this 80/20 Pareto Principle to create a “4 Blocker.” This allows you to categorize your customers in a way that makes prioritizing your sales team’s time significantly more impactful. A “4 Blocker” is a way to categorize your customer portfolio into four labels: Wheelhouse Customers, Opportunity Customers, Breakeven Customers, and Secondary Customers.
A Wheelhouse Customer is a customer that falls within the 20% of customers driving 80% of BOTH revenue and gross margin. These are your bread and butter clients that should be top priority.
An Opportunity Customer is a customer that fall within the 20% of customers driving 80% of gross margin but NOT revenue. These are opportunities to acquire more margin by coaching your sales staff to push more product to these customers. You make great margin on them, lets increase their volume!
A Breakeven Customer is a customer that fall within the 20% of customers driving 80% of revenue but NOT gross margin. These are customers that are impacting your top line, but for whatever reason are not dropping to your bottom line. These customers need to have a focus on either increasing prices or decreasing the costs of their products bought.
A Secondary Customer is a customer that does not fall in the 20% of customer driving 80% of either revenue or gross margin. These customers should be deprioritized in comparison to the other 3 categories. You will likely be surprised as to how many are in your portfolio!
Now, lets look at how to leverage Power BI to create a dynamic and real time customer rationalization tool for you and your team.
Step 1: Create/Curate your data set & load into Power BI
Make sure you have a cleaned set of data prior to loading into the Power BI desktop file. In our example video, we used Excel data. However, you can easily connect this to your live data source. To understand how to do this, please visit the documentation from Microsoft.
Using Excel as your data source, you are going to click on the “Get Data” button on the top of the screen, select Excel, then find your data source file in your folder explorer.
Next, click on the correct tab you want to load into the Power BI Desktop file and select the load button on the screen. You should see the data pop up on the right-hand side of your screen in your Fields section of your Desktop application.
Step 2: Build your Rank and Pareto Calculated Columns
The next step is going to be to create your Pareto calculations within your loaded data set. You are going to repeat this process for gross margin, as well. However, this will walk through how to build your revenue measures. Your first step is to rank your customer’s revenue against the rest of your portfolio. To do this, use the RANKX formula in Power BI to rank your customers. Based on the example data set, the formula should look as follows: Sales Rank = RANKX(‘Customer Sales & Gross Margin’,’Customer Sales & Gross Margin'[Sales Revenue],,DESC,Dense) This ranks, in descending order, the sales revenue per customer against the rest of the portfolio. Next, you are going to create your Sales Pareto metric, by adding another column to your data table. This column is going to leverage a SUMX and a TOPN function to appropriately multiply a cumulative total of sales revenue for your portfolio. The order of the cumulative total will then follow your ranking column that you have just created. The Sales Pareto formula should look as follows:
Sales Pareto = SUMX(TOPN(‘Customer Sales & Gross Margin'[Sales Rank],ALL(‘Customer Sales & Gross Margin’),’Customer Sales & Gross Margin'[Sales Revenue]),’Customer Sales & Gross Margin'[Sales Revenue]).
As you can see, the formula leverages the previous rank column to direct how the SUMX function sums the cumulative revenue total.
Step 3: Create your Pareto Percentage
The next step in building your tool will be to create your final Pareto Percentage. This will indicate what percentage of the specific metric you are using (in the example it is revenue) is being driven by the specific customer in the overall portfolio.
For this, use a simple divide function on two of the columns that are already created. You are going to divide the specific customer’s cumulative total by the total revenue of the portfolio. Since the cumulative total is calculated based on rank, you will be able to see which customers have the biggest impact on your revenue! The formula should look as follows:
Sales Pareto Percentile = DIVIDE(‘Customer Sales & Gross Margin'[Sales Pareto],SUM(‘Customer Sales & Gross Margin'[Sales Revenue]))
Note: It may be easiest to visualize this Pareto Effect by sorting your rank column from smallest to largest. You should be able to see the revenues increase as you move down your data set.
Step 4: Create Your Pareto Label
Pause here and repeat steps 1-3 for gross margin. Once that is complete, continue with Step 4.
The final step in the process is to categorize your customers based on where their Pareto Percentages fall within your portfolio for each of your metrics. Using the labels described above, categorize your portfolio using a nested IF statement. Your formula should look as follows:
Pareto Label = IF(AND(‘Customer Sales & Gross Margin'[Sales Pareto Percentile] <= .80, ‘Customer Sales & Gross Margin'[Gross Margin Percentile] <= .80),”Wheelhouse”,IF(AND(‘Customer Sales & Gross Margin'[Sales Pareto Percentile] <= .80,’Customer Sales & Gross Margin'[Gross Margin Percentile] > .80),”Opportunity”,IF(AND(‘Customer Sales & Gross Margin'[Sales Pareto Percentile] > .80,’Customer Sales & Gross Margin'[Gross Margin Percentile] <=.80),”Breakeven”,”Secondary Customer”)))
Once this is complete, you are now ready to build your Customer Rationalization Dashboard! This should be entirely set up for the success of the front-end user. AMEND recommends gaining feedback from the user of this tool on how to design the necessary visualizations to make the tool as impactful as possible.
Step 5: Build your Pareto Analysis Tool
Once you have your portfolio categorized, it is now time to build out your tool. The biggest impact visualizations are created using a scatter plot, and a bar/line combination chart. These provide great representations of the Pareto Principle, and allow you to dynamically break down your portfolio.
Below is each visualization highlighted at a high level as to what went into creating them. As stated earlier, it should be up to the discretion of the front-end user of the tool as to how to best design it for them to utilize. AMEND recommends using these visuals to leverage this analysis to its fullest capacity.
1. Metric Cards – Card Visual: Allows user to see at a high level the overall portfolio revenue and margin numbers, as well as each subset of the portfolio when broken down by the slicer.
2. Pareto Label Slicer – Slicer Visual: Allows user to filter the other visuals to see the breakdown of each visualization with just that portion of the portfolio. Very impactful in drilling into answers and displaying the impact of the 4 cohorts of customers within your portfolio.
3. Pareto Charts – Bar/Line Combo Chart Visual: Allows user to see how both revenue and gross margin are distributed across the portfolio of customers. Also gives the user the traditional Pareto line that shows the diminishing impact of each added customer.
4. Scatter Plot – Scatter Plot Visual: Allows user to see the breakdown of the customer portfolio and identifies the customers having the largest impact on your portfolio. Very powerful when paired with the label slicers (visual 2).
[cta id=’6635′]
