Deep Analysis

Finding 'Break-even points' in pricing

to protect profits using Power BI

EU Superstore: revealing stories of profit and loss across Europe

Project Overview

This project investigates the impact of discount strategies on net profitability across a European retail dataset. By leveraging Power BI and DAX, I identified the exact "tipping points" where discounts transition from promotional tools to value-destructive losses. These insights facilitate high-level forecasting and data-driven pricing strategies.

Situation

The dataset contains over 10,000 transactional logs from a European retail entity (France, Germany, UK) between January 2015 and December 2018. Upon initial review, I identified a concerning trend: significant financial losses were frequently occurring alongside high-volume discounts in the Furniture and Technology sectors.

Task

Recognizing that these losses were unsustainable, I took the initiative to identify the Break-even Point—the exact discount threshold where net profit hits zero. My goal was to provide a data-backed roadmap to prevent further "margin leakage" and help the business distinguish between a "good sale" and a "loss-making sale." The goal was to provide a framework for future predictive modeling and optimised product mix and pricing decisions.

DAX

Interact directly with the reports above. Use the scroll bar to find the bottom where you will be able to access the other six reports. Read below as a guide to the visualisations.

📖 How to Read the "Sweet Spot" Chart

The Horizon (Zero Line): The solid black line running across the middle represents £0 Profit.
- The Profit Zone: Anything above this line is money in the bank.
- The Loss Zone: Anything below this line means the business is paying to sell the product.
The Intersection (The Sweet Spot): The exact point where the category line crosses the Horizon is your maximum safe discount. If the line crosses at 24%, any discount offered at 25% or higher is a "red" sale.
The Slope: The steeper the line (like in Furniture), the more "fragile" that category is. A small 5% change in discount can lead to a massive swing in profit.

💡 How to use the Slicers

Use the Region and Segment slicers on the left to see your local "Sweet Spot". You'll notice that while the general break-even for Technology is high, certain regions have much tighter margins. This helps managers set different discounts policies for different territories. Factors affecting these differences can be competitors or presence of corporate clients in specific regions.

Unfortunately, the dashboards are not available for sharing outside the platform due to account restrictions.

The Data Analysis Expressions

That Multiplied And Deepened The Insights

Action

I developed the following custom DAX measures to drive the analysis:

Total Profit & Profit Margin %: To assess overall financial health.
Average Discount %: To normalize discount tracking across variable order sizes.
Correlation and R2: Essential to understand how profits are impacted by discounts. While Correlation shows the direction of the relationship, R-Squared quantifies exactly how much of the profit fluctuation is driven by discounts.
Breakeven Discount %: A sophisticated measure using linear interpolation to find the exact point where the profit curve intersects the zero-axis.
Profit Colour: A conditional formatting logic to visually highlight losses in Red, providing immediate "Danger Zone" recognition.

Technical Challenges Overcome:

Environment Constraints: Navigating the specific UI limitations of the Power BI Web Version.
Data Standardisation: Resolved initial data discrepancies by adjusting the 'Locale' settings to align time zones and currency formatting.
Visual Calibration: Fine-tuned X and Y axes to ensure a continuous, relevant scale that accurately represented the relationship between percentage and currency.

Result

Interactive Geographic Map: I integrated a map visual to show profit distribution across Europe. This allows decision makers to see at a glance which countries or cities are struggling with profitability.
Reports and Dashboard: reports showing profit based performance per country, discounts given by categories. Charts and slices allow decision makers to define discounts above the breakeven points to avoid losses. With the interactive features in the dashboard they can see it by region, city and segment.
Threshold Discovery: Identified that Furniture has the lowest discount tolerance (breaking even at ~24%), whereas Office Supplies remains resilient at higher tiers.
Localised Insights: By using the Region and Segment slicers, I discovered that the "Sweet Spot" isn't universal. A discount that is profitable in the North region might be a loss-leader in the South due to underlying cost structures.
Risk Mitigation: The "Profit Colour" implementation allowed stakeholders to instantly identify unprofitable regions and segments.
Strategic Foundation: Established a baseline for future Forecasting Models, allowing the business to predict the financial impact of seasonal sales before launch.

Technical Stack

Tool: Power BI (Web/Desktop) CSV file provided by JustIT
Language: DAX (Data Analysis Expressions)
Key Features: Conditional Formatting, Linear Interpolation, Locale Configuration.
AI: I used Google Notebook LM, DeepSeek and Gemini to validate my findings. The data set is public and no GDPR regulations were broken.

Future Enhancements & Modeling

Key Influencer Analysis: Use AI-driven visuals to identify the primary drivers behind profit fluctuations, such as Seasonality (Order Date) or Shipping Costs.
Forecasting: Develop predictive models to simulate how changes in supply chain costs might shift the current break-even thresholds.
Shipping Optimization: Analyze the correlation between "Shipping Mode" and the break-even point to refine logistics strategies.

Ethics & Compliance

Data Privacy: This project uses a public dataset. No GDPR regulations were breached during the analysis.
Transparency: AI tools were used for logic validation and documentation assistance only; all data modeling and visual design were performed manually.

GitHub Repository

Original files are available here

Data Without Context Can be Misleading

Applying a maximum discount that results in zero profit—or breaking even—is generally considered a strategic, short-term tactic rather than a sustainable business model. It is appropriate when the primary goal shifts from immediate profitability to customer acquisition, market share, or operational efficiency.

Here are the specific scenarios when it is okay to apply a zero-profit discount:

To Clear Excess or Obsolete Inventory: If stock is sitting idle, costing money to store, or is about to expire, selling it at cost converts that inventory into cash, allowing you to invest in more profitable, faster-moving stock.
As a "Loss Leader" to Drive Traffic: A popular item is sold at cost (or even a slight loss) to lure customers into a store or onto a website, with the expectation that they will buy other, higher-margin items simultaneously.
For Customer Acquisition (High Lifetime Value): To acquire a new customer, you may forego profit on the first transaction, calculating that the long-term value (LTV) of that customer and future, full-priced purchases will justify the initial break-even deal.
New Product Launch/Market Penetration: When entering a new market or launching a new product, low prices are used to encourage customers to take a chance on an unknown, build awareness, and quickly generate market share.
To Maintain Market Position/Competitiveness: In a "price war," you may match a competitor’s low price, bringing your profit to zero to prevent losing customers permanently.
To Build Customer Loyalty: As a thank you to existing customers or to secure a large contract, a zero-profit, high-value offer can boost goodwill and lead to recurring, more profitable work later.

Crucial Considerations:

Know Your Costs: You must accurately calculate your Cost of Goods Sold (COGS) and variable costs. Breaking even means you are covering variable costs (materials, direct labor, shipping) but not contributing to fixed costs (rent, salaries, overhead).
Set Time Limits: Such discounts must be temporary. If they become permanent, they damage your brand image, leading customers to believe your product is "cheap" or that the list price is artificial.
Avoid Overuse: If not managed properly, customers may "cherry-pick"—buying only the discounted item and not buying additional profitable items, leading to overall losses.

Page updated

Google Sites

Report abuse