[Power BI] DAX ALL Function Practical Series ② Ranking Part 2 : Mastering Pareto Analysis - Finding Top-N Structures with DAX ALL + RANKX
The reason we learn the ALL function in Power BI is not just to simply remove filters. It is to establish a Grand Total as a universal reference point and clearly understand the relative position of each item within that context.
In the previous post, we looked at the performance structure between products through Mix (Share) and Ranking analysis. In particular, by combining the ALL and RANKX functions, we established a Global Ranking from a company-wide perspective and verified what position each product occupies within the overall sales structure.
In this post, we intend to go one step further and expand the horizon of our analysis. The protagonist is Pareto analysis, which uncovers the point of strategic decision-making by using the ALL function to clear filters for "Share," the RANKX function to grant "Order," and then "Accumulating" these results.
This process of finding the basis for "Selection and Concentration" beyond a simple listing of numbers will be a core step in expanding your analytical thinking as a data analyst.
Power BI Ranking Analysis Series
- Part 1: Global Ranking (RANKX + ALL)
- Part 2: Top-N & Pareto Analysis -- Current Post
- Part 3: Relative Rank Index
- Part 4: Cross Ranking (Store × Product)
- Part 5: Rank Change Analysis
The goal of this series is not a simple explanation of DAX, but to understand the Power BI analytical mindset by connecting DAX Design to Actual Data Calculation to Strategic Interpretation.
1. What is the Pareto Principle?
The Pareto Principle is commonly referred to as the 80/20 rule. This principle explains the following phenomenon:
"Most of the total results come from a few causes."
In other words, it is a structure where the Top 20% leads to 80% of the total results. This phenomenon is observed in various business data. For example:
- The top 20% of customers generate most of the total revenue.
- A few top products account for the majority of total sales.
- A few core issues cause most of the failures or bottlenecks.
Therefore, the key question of Pareto analysis is: "Who are the vital few that create the overall performance?"
2. Analysis Data
In this analysis, we continue to use the H1 2024 IT product sales data used in the previous post. The total sales are as follows: Total Sales = 3,801,000 USD
We will use this data to calculate the Pareto structure. The dataset includes monthly records from June to December 2024 across stores in New York, Los Angeles, Chicago, and Houston, featuring products such as iPhone 15, MacBook Air, Galaxy S24, Pixel 8, iPad Pro, Surface Pro, Galaxy Tab, Dell XPS, and Lenovo ThinkPad.
3. Calculation Structure for Pareto Analysis
Pareto analysis consists of the following three steps:
- Calculate Product Ranking
- Calculate Cumulative Sales
- Calculate Cumulative Sales Share
3.1 Product Ranking
First, we calculate the product ranking. This measure calculates a descending rank based on sales per product. This ranking is used as the basis for the subsequent cumulative sales calculation.3.2 Calculating Cumulative Sales
The core of Pareto analysis is Cumulative Sales.Cumulative Sales =
VAR CurrentRank = [Product Rank]
// Store the rank of the current product
RETURN
CALCULATE( [Total Sales],
FILTER( ALL('Sales'[Product]),
// Remove product filters to evaluate against the full product list
// This ensures the Pareto calculation is always based on the global dataset
[Product Rank] <= CurrentRank
// Include all products with a rank higher than or equal to the current product
// This creates the cumulative total of sales based on ranking
)
)
This calculation logic accumulates the sales of all products that have a rank higher than or equal to the current product.
The important point here is that Pareto analysis always presupposes a "Global Reference." If the ALL function is not used, only some products remain due to slicers or filters, and the cumulative calculation is recalculated based on partial data. In other words, the Pareto structure itself changes.
By using ALL('Sales'[Product]), you can always maintain a "Cumulative structure based on all products" regardless of the filter status of the current report. Cumulative sales calculated in this way become the core indicator of Pareto analysis.
3.3 Calculating Cumulative Share
Cumulative Share =
DIVIDE(
[Cumulative Sales],
CALCULATE([Total Sales], ALL('Sales'[Product]))
)
This measure calculates the cumulative contribution relative to the total sales. It divides the cumulative sales by the total company-wide sales, which are calculated by clearing all product filters.
4. Pareto Structure Seen Through Actual Data
Applying the expanded data yields the following results:
We can confirm an important fact here: The top 4 products account for approximately 78% of total sales. This is a typical centralized market structure close to the Pareto Principle.
5. Market Structure Shown by the Pareto Chart
5.1 Data Visualization: Constructing the Pareto Chart
It is time to visualize the calculated DAX expressions into a chart. In Power BI, Pareto analysis is usually implemented using a Line and Clustered Column Chart.
- X-Axis: Product (Product Name)
- Column (Bars): Sales (Individual Product Sales)
- Line: Cumulative Share (Cumulative Sales Percentage)
The Golden Point we must pay attention to in this chart is the point where the cumulative share line reaches 80%. The products included up to this point are the Core Product Group of our business.
5.2 Top-N Analysis: Selection and Concentration
Going one step further from Pareto analysis leads to a Top-N management strategy. In practice, we don't just stop at checking the cumulative share; we ask the following question:"What percentage of the total performance do the top N products account for?"
Through this question, we can define the Core Management Scope. For example, as seen in our data, if the top 4 products account for 78% of total sales, concentrating nearly 80% of the marketing budget or inventory management personnel on these 4 products is the most efficient resource allocation based on data.
The technique of grouping items outside a specific rank into an 'Others' group to reduce visual complexity and focus only on the core is a very important skill in Power BI.
I’ll walk you through the specifics of these know-hows in a dedicated guide below.
[Read more: Power BI Top-N Analysis and Others Grouping Strategy] - Coming soon
6. Market Structure Revealed by Data
6.1 Leader Group: Drivers Determining the Market Direction
- Products: MacBook Air, iPhone 15
- Performance: Combined sales of 2,121,000 USD (Approx. 56% share) This group goes beyond simply having high sales; it is the core engine that determines the performance of the entire brand. Small fluctuations in this product group have a massive impact on company-wide results.
- Strategic Recommendation: Secure inventory stability as the top priority, and design price defense strategies and exclusive promotion timing centered on this product group.
6.2 Challenger Group: Potential Game Changers
- Product: Galaxy S24
- Performance: Market share of 14.1% While a gap exists with the Leader group, this group has significant ripple effect as a single item. If supported by appropriate marketing, it is a growth engine that can enter the Leader group at any time.
6.3 Long Tail Group: Diversity of Demand and Niche Markets
- Products: Pixel 8, Surface Pro, Galaxy Tab, Dell XPS, Lenovo ThinkPad, iPad Pro
- Performance: Individual shares around 2% to 8% Individual sales are low, but this area satisfies the diverse needs of customers. This area requires an efficiency strategy targeting users with specific purposes or professional markets rather than mass competition.
7. Strategic Interpretation: Optimizing Resource Allocation
The essence of Pareto analysis goes beyond data visualization to provide a decision-making framework for deciding where to concentrate limited resources.
Wrapping up
The Real Meaning of Pareto Analysis
Many people understand Pareto analysis simply as the "80/20 rule." However, the more important question in data analysis is this: "Who are the vital few that create the overall performance?" The moment you answer this question, data stops being just numbers and becomes a tool for strategic decision-making.
Wha's Next
In the next post, we will cover the Relative Rank Index. This analysis answers the following question: "How can we compare the performance of stores with different sales volumes?" In other words, it is an analysis that measures relative performance rather than absolute rank. We will explore relative performance analysis techniques that are used very powerfully in Power BI.
<Other posts on the blog>
Comments
Post a Comment