[Power BI] DAX ALL Function Practical Series ① Mastering Mix & Share (Market Share & Contribution Analysis)


In the previous post, we summarized the two faces of the ALL function: the Filter Modifier and the Table Provider. Today, as our first practical pattern, we will master "Mix & Share" analysis—the most widely used technique in the field—and the design of Performance Indices derived from it.

The purpose of this post is clear: "When you calculate shares accurately, your sales strategy changes." This is not just a list of formulas. It is a full-course guide starting from DAX design based on actual sales data, moving to numerical verification, and ending with strategic interpretation.

Simple sales numbers are just the result, share is the blueprint for future growth.

 

1. Why is Mix Analysis Important?

1.1 Mix is Not Just a Simple Percentage 

Simple sales figures are merely results, but "Share" is a map for designing the future. Through Mix analysis, we obtain management answers to the following questions. We calculate share for one reason: to see the structure.
  • Store Mix: How much does each store contribute to total sales?
  • Product Mix: How much does each product contribute to the whole?
These two are not just numbers; they are the starting points for decision-making.

1.2 Analysis Data Overview

H1 2024 IT Device Sales Before diving into calculations, let's define our sales data from January to May 2024. We focus on four stores and five major product lines.
  • Stores: New York (NY), Los Angeles (LA), Chicago, Houston.
  • Products: iPhone 15, MacBook Air, Galaxy S24, Pixel 8, Surface Pro.
  • Total Sales: 2,114,000 USD (The denominator for all share calculations).

Table : Monthly sales by store

Looking at the total sales scale from Jan to May 2024:

Store performance 2024 H1

 

2. Store Sales Share Analysis (Store Mix)


"How much is our store contributing to company-wide sales?" We answer this to determine the priority of resource allocation.

Store Sales Share Analysis (Store Mix) - Simple question vs. Operational question

2.1 DAX Formula Design: Fix the Denominator


The key to share calculation is creating an "Absolute Denominator" that is unaffected by slicers.

    Store Sales Share 
            = DIVIDE ( [Total Sales],
            CALCULATE([Total Sales], ALL(Sales[Store]))
            )

  • l ALL(Sales[Store]) removes the store filter to fix the denominator to the total sum of all stores.
  • l Even if a specific store is clicked in a slicer, the baseline remains unchanged.

2.2 Analysis Results (Store Performance)


Based on the H1 2024 data, here are the calculated performance metrics by store:

Store sales share by store

Table : Store sales share by store

2.3 Marketing Insights: Strategic Insights from Data

1) New York Store: Excessive Concentration or Overwhelming Performance?

A single store in NY occupies about 38% of total sales.
  • Risk: Brand power is concentrated in a specific region; a NY economic downturn could shake company-wide results.
  • Strategy: Use it as a hub to maximize high-profit premium strategies (VIP up-selling) by prioritizing the first batch of new products here.

2) Chicago Store: Discovering Structural Weaknesses in Product Mix

Chicago (17.9%) and Houston (19.4%) remain at half the level of NY sales.
  • Diagnosis: We need to review if this low share is simply due to a smaller population or weak marketing penetration in these areas.
  • Strategy: Paradoxically, low share means the greatest growth potential. Shift some NY-centric budget to local promotions to expand brand awareness and diversify the sales portfolio.

2.4 Why Mix Analysis is Critical

Mix analysis is not just a tool for evaluating the past. It is a process of creating objective evidence for "Where should we pour more marketing budget next year?"
  • Should we grow strong areas like NY further (Maximize Profit)?
  • Should we improve the constitution of weak areas like Chicago (Risk Diversification)?

 

3. Product Mix Analysis (Product Share against Total)

Product share analysis is not just about sales rankings. It is about identifying "What exactly is driving our sales?" to check brand dependency and portfolio stability.

Product Mix Analysis - Maximize profit vs. Diversif risk

3.1 DAX Formula Design

Just like store analysis, we use the ALL function so that the denominator remains fixed at 'Total Company Sales' even when a specific product is selected.

Product Sales Share =
        DIVIDE ( [Total Sales],
        CALCULATE([Total Sales], ALL(Sales[Product]))
                            )

3.2 Product Sales and Market Share Data (Qualitative Structure)

When we break down the data, the qualitative structure of our business becomes clear at a glance:

Product sales share by product

Product performance : 2024 H1

3.3 Marketing Insights: Strategic Decisions from a Product Perspective

Marketing Insights: Strategic Decisions from a Product Perspective

1) Apple Brand Dependency Risk (61.0%)

Combining iPhone and MacBook shares accounts for 61% of total sales.
  • Status: Brand power is very strong, creating stable profits.
  • Risk: Exposed to "Single Brand Risk" where over 60% of total company revenue could be immediately hit if Apple faces product delays or supply chain issues.
  • Strategy: While securing profitability from Apple products, gradually increase the share of other brands (Samsung, Google) to ensure portfolio stability.

2) Surface Pro Positioning Failure Analysis

With only a 6.5% share, the Surface Pro is the weakest link in our lineup.
  • Diagnosis: We need to review if it’s simply unpopular or if the appeal to target customers is lacking.
  • Strategy: Execute a niche market strategy centered on specific locations or use bundling discounts to increase turnover.

3.4 Practical Tip: The Purpose of Product Mix Analysis

Product Mix analysis is not just about recording what sold a lot. It determines inventory priority and helps optimize marketing ROI by identifying if excessive advertising dollars are being spent on low-share products.

 

4. Store × Product: Cross Mix Structural Analysis (Advanced)


"iPhone sells well in NY, but why does it underperform in Chicago?" Cross Mix analysis combines store geographic characteristics with product preferences to derive customized strategies for each store.

4.1 Core Measure Design for Cross Mix

1) Internal Product Share (Structural Metric):


Product Share in Store =
        DIVIDE( [Total Sales],
        CALCULATE( [Total Sales], ALL(Sales[Product]))
-- Removes product filters to fix the Store Total as the denominator
            )

Internal Product Share (Structural Metric):

This shows what customers primarily buy when they enter a specific store.

2) Store × Product Share vs. Company (Contribution Metric):


    Store Product Share (vs Company) =
        DIVIDE( [Total Sales],
            CALCULATE( [Total Sales],
                ALL(Sales[Store]),
                ALL(Sales[Product])
                )
-- Removes both Store and Product filters to fix Corporate Total
            )
    
Store × Product Share vs. Company

This shows where the real money is being made relative to the corporate total.

4.2 Strategic Insights: Premium vs. Value


Store Product share (vs. company) product share in store and total sales by product and store

1) Understanding the Axes

  • X-Axis (Store Product Share vs Company): Shows the relative strength of that specific store's product compared to the company average. If it's to the right of 7.7%, it's a "High Contribution" item.
  • Y-Axis (Product Share in Store): Shows the influence of that product within that specific store. If it's above 30.8%, it's a "Core SKU" for that store.
  • Bubble Size: Absolute revenue scale.

2) Detailed Analysis by Quadrant

  • Top-Right (Strategic SKUs) - "The Engines": (e.g., NY-iPhone 15, NY-MacBook Air). High contribution to the company and high influence in the store. Strategy: Prioritize stock and maximize profit via VIP up-selling.
  • Top-Left (Local Niche SKUs) - "Local Powerhouses": (e.g., Chicago-Pixel 8, Chicago-Galaxy S24). Low overall contribution but very high local share (around 40%). Strategy: Focus on hyper-local promotions and Android-centric lineups for these strongholds.
  • Bottom-Right (Growth Potential SKUs) - "Potential Stars": (e.g., LA-iPhone 15). High company contribution but near average local share. Strategy: Increase display space to pull up local market share and drive company revenue.
  • Bottom-Left (Restructuring Targets) - "Inefficient Weaknesses": (e.g., Surface Pro across all stores). Low on both axes. Strategy: Avoid excessive marketing; consider bundling or SKU optimization (reduction) to save costs.

3) Final Strategic Recommendations from Data

  •  Risk Diversification: Dependency on NY-iPhone 15 is over 23%. Strategically cultivate the Android market in Chicago/Houston to diversify the portfolio.
  • Optimal Resource Allocation: Maintain conservative safety stock for Top-Right products to prevent opportunity loss, and strictly audit ROI for Bottom-Left products.

4.3 Practical Purpose of Cross Mix

The purpose is optimization. Are we placing SKUs that don't sell in NY? Reduce poorly performing SKUs and concentrate on successful combinations. This significantly changes inventory turnover and marketing ROI.

 

5. Internal Benchmarking: Performance Index Analysis

We measure relative strength, not absolute sales, to see "Who is stronger than the average?"

5.1 Measures for Performance Index Design

1) Average Store Share:


Avg Store Share =
        AVERAGEX( ALL(Sales[Store]), [Store Sales Share] )

2) Performance Index:

Store Share Index =
        DIVIDE([Store Sales Share], [Avg Store Share]) * 100

5.2 Marketing Strategy Based on Numbers

Marketing Strategy Based on Numbers
  • New York (Index 150.4) - Best Practice: Use as a hub to share success factors (layout, staffing) with other stores.
  • Houston (77.6) / Chicago (71.6) - Risk Groups: These stores are below the company's average capability. They need product mix redesigns and aggressive local awareness campaigns.
  • Practical Point: This index serves as a fair basis for incentives. Above 110 = Bonus; Below 90 = Improvement Plan required.

 

Warpping up


We started with a single ALL function and secured all the following insights:
  • Current Status: Share by store and product (ALL as Filter Modifier).
  • Structural Analysis: Compatibility between stores and products (Nested ALL).
  • Performance Evaluation: Report card against company average (ALL as Table Provider).
Mix analysis is not just a percentage calculation. It is a brand risk management tool, a basis for budget reallocation, a standard for inventory strategy, and a baseline for performance evaluation. ALL is the function for designing strategic standards.





Comments

Post a Comment

Popular posts from this blog

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more

Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy

Image
Standard Deviation is a core metric that quantifies the 'dispersion' of your data, showing—as a single number—how tightly clustered or widely spread the data points are from the mean. A larger number means the data is widely spread out, while a smaller number means it's tightly grouped. Let's explore how this concept is used strategically in analyzing sales and profits. If you missed 'Standard Deviation' first contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis A nd you can also refer next contents, after reading this. Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis   1. Why We Must Read the 'Wobble' (The Trap of the Mean) It's natural to first look at average sales or profit when analyzing a company's performance. The average is the central value of the data, making it the most fundamental and useful metric for gr...
Read more