We are often asked by suppliers, what determines which products get ranged in which stores and how can we influence the decision? 

Have you ever noticed that 80% of your results come from just 20% of your efforts? Welcome to Pareto’s Law, where a small portion of your actions can yield the most significant impact. Understanding this rule will transform how you approach retail analytics and allow your brand to stand out with needle-moving suggestions backed with data. 

This article will explore a practical 80/20 use case that consumer brands frequently face – Creating an item-level analysis to uncover which portions of the assortment disproportionately produce the most revenue.

Brands start each season with a collaborative planning process in which volume objectives are agreed upon with the retailer. From here, the brand attempts to figure out a supply plan to flow inventory through its distribution center(s) and back to the retailers’ stores. Inevitably, the best-selling items will run out of stock, and the slowest-moving items quickly become excess inventory. To mitigate the stock-out risk, brands invest in safety stock.

Using Pareto’s Law, brands can determine which items to take a deeper inventory position. Let’s walk through a simple example using Power BI.

 

Step 1: Pull a simple P.O.S. dataset containing date, store, item, and sales. A sample of data is shown below. The actual dataset contains 10,000 rows. 

 

Step 2: Roll up the data on the item level and sum the total sales — a sample of how the aggregation looks are below. The items are ranked from highest revenue to lowest. 

 

Step 3: Cumulative total calculations work best against temporal or numeric values, given the need to sort. Our example deals with a text data type, item. To allow for proper sort order, a “helper” column is created that ranks each items aggregated sales figure. 

The DAX driving this column looks like this. 

 

Step 4: Now that we have a numeric field to sort our item level sales by, we can calculate the cumulative sales value. 

The DAX powering the column ‘Product Running Total Final’ can be seen below. 

 

Step 5: Convert the column ‘Product Running Total Final’ to a percent. 

Here is the DAX for the column ‘% Running Total Product.’ 

 

Step 6: Let’s visualize how Pareto’s Law works on our dataset. The image below highlights the 27% of the items (38 of 141 total items) in the assortment contributing 80% of sales. From here, the brand can take the subset of high-performing items and work with the supply team to take a more significant inventory position. 

 

Step 7: Pairing fast-moving products alongside retail inventory distribution will help the brand make a case for the merchant to invest more in top items. Repeating steps 1-6 based on inventory units, not sales, and one final data merge produces the below graphic (sample).. The imbalance is clear – inventory is not keeping up with the top-moving items. 

 

 

Pareto’s law is only half of the equation.

Any business analyst worth their salt will take the extra step to diagnose why the 38 items in our analysis are driving so much business. The merchant team will want to understand the underlying demand drivers before raising their buy outlook. This conversation won’t end well if you can’t tie the trend to sustainable reasons. 

• • Is your brand running national marketing campaigns for the top items?
  • Is the consumer buying trend from your market share data showing shoppers trading up/down?
  • Are the other 80% of underperforming items chronically out of stock?
  • Are pay-per-click marketing campaigns driving omnichannel business? 

The framework presented in this article requires a few ingredients – P.O.S. data and Power BI DAX chops. Pulling this data from multiple retail portals is a significant hurdle. This is precisely where the SaaS community can step in, such as Krunchbox. Pass off the hassle of acquiring P.O.S. data at scale, and focus on extracting value from the data.