Retail Sales Performance Analytics
Uncovering that 36% of all orders were loss-making despite $2.27M in revenue — and pinpointing exactly which discounts, categories and sub-categories were destroying margin.
Dataset
Sample Superstore
Rows
9,994
Period
2014 – 2017
Tools
Power Query, SQL Server, DAX
$2.27M
Total revenue (2014–2017)
36%
Orders were loss-making
-40.6%
Avg margin at 30%+ discount
12.45%
Overall profit margin
The problem
A US-based retail company was generating over $2.27 million in revenue across four years but struggling with profitability. Management had no clear visibility into which products, regions, or customer segments were actually making money — and which were quietly destroying it. Decisions were being made on revenue alone, without understanding margin, loss rates, or the true cost of the company's discounting strategy.
"High revenue masked serious profitability issues. No one knew that 36% of all orders were loss-making."
The dataset — 9,994 rows across 21 columns — had no pre-calculated margin, loss flags, or discount band segmentation. Every profitability insight had to be built from scratch: Power Query for cleaning, SQL Server for aggregation, and Power BI with DAX for the final interactive dashboard.
Discount bands vs. average profit margin — the core diagnostic chart of the dashboard.
Business questions answered
Category profitability
Technology leads at 17.39% margin. Furniture generates nearly identical revenue (~$730K) but only 2.32% margin.
Discount impact
No-discount orders average +34.0% margin. At 30%+ discounts, average margin drops to -40.6% — the company is paying customers to take products.
Regional performance
California generates the highest state revenue, but several Central region states consistently post negative margins.
Key findings
- 36% of all orders are loss-making — 1,808 out of 4,931 orders, making profitability improvement the single highest-value opportunity for this business
- Discounts above 30% produce an average margin of -40.6% — every percentage point of discount above 30% costs more than it generates in additional volume
- Furniture earns 2.32% margin vs Technology's 17.39% — both generate ~$730K in revenue, but the profitability gap is entirely a mix-shift opportunity
- Tables and Bookcases together lose $21,000+ annually — revenue figures look healthy but mask deep, avoidable losses
- Q4 consistently peaks across all four years — a predictable seasonality pattern useful for inventory and staffing planning
Business recommendation
Stop discounting Furniture sub-categories beyond 20%. Reprice or discontinue Tables and Bookcases entirely. Reallocate promotional budget toward Technology, where margins are healthy. Use the regional loss map to correct pricing in loss-making Central region states, and segment future campaigns by Corporate vs Consumer — Corporate clients deliver better per-order margins and more consistent repeat purchase rates.
