DEV Community: Rishab Arya

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

Rishab Arya — Mon, 28 Sep 2026 12:08:41 +0000

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.

From CSV to Dashboard: Customer Shopping Trends Analysis with Python, PostgreSQL, and Power BI

Rishab Arya — Mon, 28 Sep 2026 11:59:57 +0000

A retail company wants to understand its customers' shopping behavior to improve sales, satisfaction, and loyalty. I built this end-to-end analytics project to answer that question with a modern data-analyst stack: Python → PostgreSQL → Power BI.

The business problem

How can the company leverage consumer shopping data to identify trends, improve customer engagement, and optimize marketing and product strategies?

The analysis answers 10 business questions covering revenue by gender and age group, discount behavior, subscriber economics, shipping preferences, product ratings, and loyalty segmentation.

What the data looks like

3,900 synthetic retail purchase records
18 columns: purchase amount, review rating, gender, age, category, item purchased, payment method, subscription status, shipping type, etc.

Pipeline

CSV (3,900 rows)
  ↓ pandas — cleaning & feature engineering
PostgreSQL via SQLAlchemy
  ↓ 10 business questions solved in SQL
Power BI Desktop
  ↓ KPI cards, donut, 4 charts, 4 slicers, explicit DAX measures
Report + presentation

What I fixed vs. the tutorial

The tutorial this project was based on plotted some visuals using Sum(customer_id), which produced meaningless totals in the millions. I audited every binding and replaced those with explicit DAX measures like:

Number of Customers      = COUNT('public customer'[customer_id])
Average Purchase Amount  = AVERAGE('public customer'[purchase_amount])
Average Review Rating    = AVERAGE('public customer'[review_rating])

Five key findings

Finding	What the data shows
Clothing drives revenue	$104,264 of $233,081 total (44.7%)
The gender gap is audience size	Male $157,890 total, but $59.54 vs $60.25 per customer
Subscriptions don't raise basket size	$59.49 subscribed vs $59.87 non-subscribed
Discounts don't grow baskets either	$59.28 discounted vs $60.13 full-price
Loyal base, thin acquisition funnel	3,116 loyal (79.9%) vs 83 first-time buyers (2.1%)

Tech stack

Layer	Tool
Cleaning & ETL	Python (pandas)
Storage & SQL	PostgreSQL
Visualization	Power BI Desktop (DAX)
Reporting	PDF + PowerPoint

Repo, report, and reproduction

GitHub: Customer-Shopping-Trends-Analysis
Report: included as PDF and executive deck in the repo
Reproduce: README has exact steps, including setting PG_PASSWORD before running the notebook

This was my first end-to-end portfolio project. The biggest lesson was not the syntax — it was learning how to translate a business question into an ETL + SQL + dashboard chain that produces an actionable answer.