Supply Chain Service-Level Dashboard: An FMCG Case Study with Power BI and DAX

#powerbi #businessintelligence #dataanalysis #fmcg

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.

DEV Community