Power BI DAX Integrated Analysis to Fix the Average Trap (Part 1-3): Segment Efficiency Integrated Analysis: Strategic Decision Report Based on EI-M and EI-R (A Power BI + DAX Approach)

This article provides practical insights utilizing Power BI and DAX to quantitatively assess the management efficiency and structural risk of customer segments, translating directly into strategic decision-making. Our goal is to move beyond simple sales volume and simultaneously understand the typical patterns and stability of customer behavior to optimize resource allocation and marketing strategy.

Previous analysis used the Power BI Box & Whisker with Points chart to visualize purchasing behavior across segments and identified stable purchasing groups through the Median and IQR. However, that approach had limits in explaining the structural value and risk of extreme customer groups like VIPs and Regulars.

(If you missed it, please check out below first)

Power BI DAX Integrated Analysis to Fix the Average Trap (Part 1-2): Median-driven Segmentation Strategy using Power BI's Box and Whisker Chart

Therefore, this report integrates an analysis of internal structural stability (EI-M, EI-R), moving beyond mere outward efficiency (P2/P1), and provides concrete, actionable insights for real-world strategy, all powered by Power BI and DAX.

1. Outward Efficiency Analysis (P2/P1)

In this section, we analyze the outward efficiency of customer segments using Power BI and DAX measures and visualize the strategic insights using a bubble chart.

1.1 DAX Definition for Efficiency Analysis (Power BI Measures)

The following measures were created in Power BI for the outward efficiency analysis:

1) Customer Count: Calculates the number of unique customers from the 'CustomerID' column in the 'Sales' table, representing the segment's scale.



DISTINCTCOUNT function calculates the number of unique values in a column, excluding duplicates. In this case, we use it to accurately find the number of customers who actually made a purchase, ignoring repeat buys.

2) Total Revenue (K): Calculates the sum of all sales in the 'Purchase Amount (K)' column, representing the segment's outward size.


SUM is the most fundamental aggregation function; it simply totals all the numbers in a specific column. Here, we use it to calculate the entire revenue generated by a customer segment.

3) Customer Share (P1): Calculates the segment's relative customer proportion by dividing the current context's customer count by the total customer count.



DIVIDE function performs the division operation, but its key benefit is preventing errors by returning a specified value (in this case, 0) if the denominator is zero.
CALCULATE is a powerhouse function that forces the measure supplied as its first argument (like [Customer Count] or [Total Revenue (K)] to be evaluated within a specific filter context that we define.
ALL('Sales') is overriding any existing filters applied to the 'Sales' table, allowing the calculation to refer to the entire table. This is how we accurately determine the segment's customer proportion against the total customer count.

4) Revenue Share (P2): Calculates the segment's relative revenue proportion by dividing the current context's total revenue by the overall total revenue..



Just Like with P1, we use the combination of Divide and Calculate/All structure to figure out the segment's revenue share against the whole. This is how we guarantee a precise, total-based ratio for all our segment breakdowns.

5) Outward Efficiency (P2/P1): Measures the relative efficiency of a segment's Revenue Share (P2) versus its Customer Share (P1). It evaluates "how efficiently each segment contributes to outward revenue."

• P2/P1 > 1 (High Efficiency): High Efficiency. The segment's revenue share is greater than its customer share, meaning the average contribution per customer is high.
• P2/P1 = 1 (Balance): This segment's customer share is equal to its revenue share, which means it provides an average level of contribution.
• P2/P1 < 1 (Low Efficiency) : The segment's customer share is greater than its revenue share, meaning the average contribution per customer is low.

1.2 Results of P2/P1 Efficiency Analysis

The analysis revealed a stark difference in the outward efficiency of the customer segments.

1.3 Utilizing the Power BI Bubble Chart

This chart integrates three core segment defining metrics—Scale (P1), Revenue Contribution (P2), and Efficiency (P2/P1)—into a single visual image.



1) Compressed and Integrated 3D Metric Analysis:

• P1 (Customer Share) is placed on the X-axis, and P2 (Revenue Share) is on the Y-axis.
• The most critical strategic metric, Efficiency (P2/P1), is assigned to the size of each segment's bubble. This method allows users to grasp and compare the segment's customer scale, revenue contribution level, and productivity simultaneously on a single screen without needing to cross-reference multiple tables or graphs. For instance, the small X-axis position and overwhelming size of the VIP bubble instantly communicate the fact that 'a small number of customers contribute a very high proportion of revenue with high efficiency.'

2) Understanding Relationships via Relative Segment Positioning:

• A key strength of the bubble chart is providing relative positional information—how the segments are arranged on the 2D plane. This visualizes strategic relationships between segments that are often unclear in table data.
• The Loyal segment is positioned between the Premium and Regular segments, residing in an intermediate zone that connects their characteristics.
• This position reveals that Loyal customers are a strategic gateway segment, offering both an upsell opportunity to Premium and a dual risk of churn to Regular if management fails. Consequently, retention and growth strategies for the Loyal customer base become critically important.

In this way, the bubble chart becomes a powerful tool that helps us interpret data intuitively, allowing for an integrated understanding of each segment's characteristics, risks, and growth potential, and ultimately enabling us to set strategic priorities effectively.

---

 

2. Limitations of Outward Efficiency (P2/P1)

The P2/P1 analysis evaluated VIP (6.28) as the ultra-high-efficiency group and Regular (0.58) as the low-efficiency group. However, a fundamental limitation emerged: outward performance alone cannot explain internal stability.

• VIP Risk: While P2/P1 is a dominant 6.28, if this revenue is generated by just one ultra-high-value purchaser, their churn means 25.1% of revenue vanishes—a high-risk, "ticking time bomb" structure.
• Regular Potential: P2/P1 is low, but if 60% of customers maintain a purchasing habit of frequent, small-amount purchases with low churn, they could be a hidden treasure: the most predictable segment, easily managed by automated CRM.

Therefore, we conclude that P2/P1 alone is insufficient to accurately evaluate a segment's structural risk and sustainability.

2.1 Goals of This Analysis

To overcome the limitations of the P2/P1 analysis, we will use the statistical tools learned previously (Median and IQR) to answer the following three questions, which constitute the final goal of this report

---

 

3. Defining Internal Structural Efficiency Metrics (Using Median and IQR)

The new efficiency metrics, EI-M and EI-R, utilize the Median and Interquartile Range (IQR) to measure 'Stability' and overcome the limitations of traditional average-based analysis (P2/P1).

These metrics evaluate not just "how much was bought," but how consistent the purchasing pattern is and how balanced the segment structure is.

• The Median is Typical Purchase Level
• The IQR is Behavioral Difference between Customers
• Ratio of IQR to Median is Key Indicator of Segment 'Quality' (Stability)

3.1 EI-M (Monetary Efficiency, Internal Management Efficiency)

EI-M measures how stable and consistent an individual customer's purchasing behavior is. It is the management efficiency indicator that predicts the predictability of marketing investment outcomes.


1) DAX Measure: Compares the segment's variability (IQR) against its Median to assess "how stably value is being generated."

• A Higher value means the IQR is smaller relative to the Median; customer purchases are highly consistent and predictable. This implies low ROI volatility for marketing campaigns, indicating good management efficiency.

•  Ideal State: High Median, Low IQR $\rightarrow$ Stable, High-Value Segment (High Efficiency)

• Inefficient State: Low Median, High IQR $\rightarrow$ Unstable, Low-Efficiency Segment (Low Efficiency)

2) EI-M Practical Application and Strategic Insight

A. Marketing Investment Stability: EI-M is used to assess performance predictability and risk levels when allocating marketing budgets.

B. Long-Term Retention Priority: EI-M helps determine the cost-effectiveness of customer retention, setting retention strategy priorities.

EI-M quantifies the stability (Consistency) of purchasing behavior within a segment, making it a practical metric for predicting marketing investment risk and setting retention strategy priorities. It is most effective when managing Median and IQR together in tools like Power BI.

3.2 EI-R (Range Efficiency, Structural Revenue Balance)

Unlike EI-M, which deals with individual customer purchase stability, EI-R evaluates how balanced and low-risk the segment's overall structure is. Power BI uses EI-R to identify revenue concentration risk by assessing structural stability relative to segment Scale (Customer Count).

1) DAX Measure:




This measures stability considering scale. A low customer count (like VIP) can inflate the EI-R. Therefore, for a large segment (Regular) to achieve a high EI-R, its IQR must be extremely small. This metric acts as a strict scrutiny of structural stability (revenue balance) relative to scale.


2) EI-R Practical Application and Strategic Insight

A. Structural Revenue Stability / Risk Assessment : EI-R diagnoses risk by showing the degree of revenue concentration.

B. Balanced Growth Strategy Design: Combining EI-R and EI-M allows for the design of segment-specific strategies.

C. Expected Effect : EI-R evaluates the stability of the overall segment structure, revealing risks that are not visible through the simple P2/P1 outward metric. Combined with EI-M, it simultaneously evaluates typical purchasing power and structural balance, supporting direct decision-making in retention, upselling, budget allocation, and high-risk segment management.

• The EI-R metric assesses the stability of the segment's entire structure, revealing risks that are simply not visible when looking at the outward P2/P1 measure alone.
• For EI-M combination:"When combined with EI-M, we can simultaneously evaluate both the typical purchasing power and the segment's structural balance."
• For practical use: In the real world, this information can be directly applied to various decisions, including retention, upselling, campaign budget allocation, and high-risk segment management.

---

 

4. Integrated Analysis Results

Integrating the three metrics (P2/P1, EI-M, EI-R) clearly revealed the structural risk and potential that were hidden when only P2/P1 was observed.





The scatter bubble chart below integrates: X-axis - EI-R (Structural Balance), Y-axis - EI-M (Internal Stability), and Bubble Size - P2/P1 (Outward Efficiency).

The chart divides the segments into a High-Stability Group (Upper Y-axis) and a Low-Stability Group (Lower Y-axis), and a High-Balance Group (Right X-axis) and a Low-Balance Group (Left X-axis), forming a segment portfolio.

4.1 Marketing ROI and CRM Strategy

1) Premium Segment: ROI Prediction Optimization & New Service Pilot

• Diagnosis: Premium has High EI-M (6.00, Very Stable), meaning its purchasing behavior has low variability and the highest predictability.

• Strategy: Utilize this consistency to maximize confidence in A/B test results. They are the optimal pilot group for testing price elasticity of new high-value services. Resources should be prioritized here for long-term LTV recovery campaigns.

An A/B Test (A/B Testing) is an experimental method where you divide customers into two groups (A and B), show them different marketing strategies, pricing, promotions, UIs, or content, and then statistically verify which option yields better results. Essentially, it's a scientific experiment to determine which strategy delivers a superior ROI.



2) Regular Segment: Gradual AOV Improvement via Small Bundling & Purchase Cycle Increase

• Diagnosis: Regular has Highest EI-M (7.00) but the lowest Median (8K)—a stable structure with small individual transaction value.
• Strategy: Apply automated recommendation logic targeting (1) low-priced/value-for-money bundles (to induce small additional purchases) and (2) an increase in purchase frequency, all based on the Median (8K). The goal is to gradually improve AOV (Average Order Value) by about 15%.

3) VIP Segment: High-Touch Risk Management & Purchase Stabilization Campaign

• Diagnosis: VIP has Low EI-M (1.00, Unstable). Their purchase pattern is extremely volatile. The IQR is similar to the Median, indicating very high performance volatility.
• Strategy: Since generic mass marketing is inefficient, maintain a Marketing Freeze. Implement an immediate, personalized High-touch Care System upon the detection of any churn signal. Aim for purchase stabilization by designing VIP-exclusive subscription/membership tiers to reduce IQR and raise the Median.

4) Loyal Segment: LTV Maximization via Automated CRM & Cross-selling

• Diagnosis: Loyal has High EI-M (6.00, Very Stable) and is a balanced cash cow with P2/P1 near 1.
• Strategy: High predictability allows for a low-cost automated CRM strategy to minimize churn and maximize cost efficiency. Focus on maximizing LTV (Lifetime Value) by increasing total purchase frequency through high-frequency Cross-selling promotions.

4.2 Financial and Risk Management Strategy

1) VIP Segment: EI-R Based Revenue Concentration Risk Warning & Diversification Policy Design

• Diagnosis: VIP has Low EI-R, meaning the Concentration Risk is extremely high. Churn of a single customer significantly increases overall revenue volatility.
• Strategy: Set a risk warning signal if VIP revenue share exceeds 30%. Design a management system where the Finance team automatically increases the budget for nurturing the Premium segment.

2) Premium Segment: Highest ROI and VIP Risk Diversification Based on High EI-M/EI-R

• Diagnosis: Premium is the ideal growth engine with both High EI-M and High EI-R (2.00, High Buffer). It has low performance volatility risk and high structural stability.

• Strategy: Designate them as the core portfolio for diversifying VIP revenue concentration risk. The Finance team should set the LTV recovery rate as the core KPI and monitor the LTV-to-CAC ratio to manage long-term investment suitability.

3) Loyal Segment: Cost Efficiency and IT System Pilot Based on Stable Cash Flow

• Diagnosis: Loyal has High EI-M and a narrow IQR, generating highly stable cash flow.
• Strategy: Utilize this stability to build an automated tier-based reward system instead of costly High-touch services. Select them as a stable pilot group for new IT system introductions (e.g., subscription platforms) to measure system ROI.

4) Regular Segment: Low-Efficiency Structure Improvement & Selective Marketing Channel Deactivation

•  Diagnosis: Regular has a large customer count but low P2/P1 and EI-R, indicating an urgent need for AOV improvement and a low-efficiency structure.

• Strategy: For customers where long-term P2/P1 improvement is difficult or ROI is low, marketing costs should be optimized through Selective Deactivation (Churn).

4.3 Product Planning and Sales Strategy

1) Premium Segment: High-end Product Planning and Exclusive Experiences Based on Q3

• Diagnosis: Premium has a high Median and Q3, where Q3 represents the actual acceptable upper price limit.
• Strategy: Plan new High-end / VIP Experience products based on Q3, ensuring the price does not exceed this limit. Maximize loyalty and upgrade inducement by offering exclusive access to new product Beta tests.

2) Regular Segment: Churn Prevention Trigger and Repeat Purchase Inducement Based on Q1

• Diagnosis: Regular has a low Median (8K), primarily consisting of small transactions.
• Strategy: Products suitable are trial/set packages or subscription/repurchase incentives. Q1 is set as the Churn Boundary (D-Zone), automatically triggering Re-activation coupons and personalized small-value promotions when purchases fall below Q1.

3)Loyal Segment: Adjacent Product Bundle Planning Based on Median

• Diagnosis: Loyal has High EI-M and a Median (60K) representing the typical purchase value.
• Strategy: Analyze adjacent product categories close to the Median and plan 3-Piece Bundles. Launch them within the IQR price range to encourage additional purchases. Increasing total purchase Frequency is set as the core KPI.

---

 

5. Wrapping Up

The strategy for resource allocation must now be reorganized based on 'Management Efficiency and Stability,' not 'Sales Size.' Maximizing overall efficiency and risk-hedging capabilities can be achieved simultaneously by stably maximizing profits in High-EI-M segments and executing proactive risk defense in Low-EI-M segments. This analysis, centered on the median, enabled strategic judgment based on the mid-level of typical customer behavior.

The next step is to further refine the efficiency metrics using the Mode (Most Frequently Occurring Purchase Amount) of each segment. Mode analysis will help us identify typical repeat purchasing habits, allowing for granular adjustments to small-value bundling, upselling strategies, and CRM message personalization.

This dual analysis, combining the Median and the Mode, ensures that both the middle tendency and the most typical pattern of customer behavior are considered. This increases the accuracy of segment strategy formulation and simultaneously achieves the goals of stable revenue generation and efficient risk management.

<Other posts on the blog>

The Hidden Hero of Data Analysis: The Mode (Part 1) : Moving Beyond the "Average Trap" to Read Your "Real Customers"

Power BI DAX Integrated Analysis to Fix the Average Trap (Part 1-2): Median-driven Segmentation Strategy using Power BI's Box and Whisker Chart

A Comprehensive Exploration into Forecast Accuracy %

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

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

DAX CALENDAR Function Deep Dive and Practical Usage Guide

The Complete Guide to the ADDCOLUMNS Function: Extending Tables with DAX