The Challenge
A major retail organization needed comprehensive visibility into their markdown ("RedSticker") program across multiple divisions, categories, and vendors. The legacy reporting system provided only monthly summary data, making it impossible to identify underperforming areas or optimize markdown timing.
Key Business Questions
- How are markdowns performing vs. budget across CA-CORP and CA-FRAN divisions?
- Which vendors and brands are driving markdown variance?
- What is the RedSticker ratio trend over fiscal periods?
- Which stores and items have the highest markdown exposure?
The Solution
I designed and built a comprehensive 4-tab Power BI dashboard that provides executive-to-analyst visibility into the entire markdown operation:
📊 Overview Tab
Executive-level KPIs showing $5.1M YTD vs $5.8M budget (−$690.5K variance). Includes division breakdown between CA-CORP and CA-FRAN, plus National vs Private brand analysis.
📈 Details Tab
RSM-level performance with budget vs actual trending by fiscal period. Interactive filters for Division, Department, Category, Subcategory, Vendor, and Brand.
🔍 Drilldowns Tab
Multi-dimensional analysis with stacked visualizations showing markdown composition across organizational hierarchies.
🏪 Top 50 Tab
Pareto analysis identifying the top 50 items and stores contributing to markdown volume.
Dashboard Views
Technical Implementation
Data Model
Star schema with fact tables for markdown transactions and budget targets. Dimension tables for time, product hierarchy, geography, and vendor.
Key Measures
- RedSticker Amount YTD
- Budget YTD
- Actuals vs Budget YTD
- RedSticker Total Ratio
Filters & Slicers
- Fiscal Year / Period / Week
- Division / Department
- Category / Subcategory
- Vendor / Brand
- RM / RA (Retail Manager/Analyst)
Performance
Optimized DAX calculations with aggregation tables for sub-second refresh on 2M+ transaction rows.
The Results
"The RedSticker dashboard transformed how we manage markdowns. We can now catch budget overruns at the category level before they become material issues."