AtliQ Mart, an FMCG manufacturer in Gujarat, is expanding to new cities — but key retail customers are declining renewals because of late and incomplete deliveries. I built a five-page Power BI dashboard that tracks the five standard supply-chain KPIs — OT%, IF%, OTIF%, LIFR, VOFR — against negotiated targets.
The business question
Where is order fulfillment failing — for whom, how badly, and why — so the supply chain can be fixed before the expansion replicates the problem in new cities?
Pipeline
- Data: 6 CSVs (31,729 orders, 57,096 lines)
- Cleaning: Power Query (BOM removal, two date formats, capitalization, on_time / in_full flags)
- Model: Star schema with 6 single-direction relationships, no fact-to-fact joins
- Measures: 19 explicit DAX measures — no implicit aggregations
- Dashboard: 5 pages — Overview, Customer Performance, Trends, Product Insights, Definitions + Data Model
Key findings
| Finding | Numbers |
|---|---|
| Promised reliability is not being met | OTIF 29.0% vs 65.9% target; 0 of 35 customers on target |
| Two failure modes | Big-3 chains collapse on-time (~29% OT); smaller customers fill only ~40% |
| Problem is systemic | City OTIF is 27.8–30.1%; every city fails the same way |
| Shortages are small and partial | LIFR 66.0% but VOFR 96.6% |
| Lateness is frequent but shallow | 28.9% lines late, avg 1.7 days, max 3 days |
Why I used explicit DAX measures
The original single-page analysis was split into five pages and rebuilt with explicit DAX so every KPI, target, gap, and color rule is transparent. Example:
OTIF % = DIVIDE ( SUM ( fact_orders_aggregate[otif] ), [Total Orders] )
OTIF Gap = ( [OTIF %] - [OTIF Target %] ) * 100
OTIF Gap Color =
VAR GapPoints = [OTIF Gap]
RETURN
SWITCH ( TRUE (),
GapPoints >= 0, "#63BE7B",
GapPoints >= -10, "#FFEB84",
GapPoints >= -25, "#FDAE61",
"#F8696B"
)
Takeaway
The dashboard points to two parallel fixes: a dispatch/scheduling workstream for on-time failures, and a supply-planning workstream for in-full failures. Vadodara is the weakest and highest-volume city, so it's a good pilot location.
Repo, report, and reproduction
- GitHub: FMCG-Supply-Chain-Dashboard
- Full report (PDF):
report/AtliQ_Mart_Supply_Chain_Report.pdf - Executive deck (PPTX + PDF):
report/AtliQ_Mart_Supply_Chain_Executive_Deck.pptx·report/AtliQ_Mart_Supply_Chain_Executive_Deck.pdf
This was my second portfolio project. The biggest lesson was moving from a messy one-page report to a clean star schema with explicit DAX: once the model is right, the insights become obvious.
