Capstone: 50k-row retail dataset — Power Query model + 4 pivots + 3 charts + insight summary

"How did FY2025-26 go compared with last year and with target — and where should we focus next year?"

Your deliverable: one workbook with a refreshable data model, 4 pivots, 3 charts, a dashboard with slicers, and a half-page insight summary.

1. The data (resources/capstone)

Deliberate problems (as in real exports): ~1,000 StoreIDs with a trailing space, ~240 blank Discounts, dates as Indian text.

2. Step 1 — Power Query (Module 3) · ~12 min

1. Put the folder path in a named cell FolderPath; create pFolder (Module 3 Lesson 7).
2. stg_Sales: From Folder → filter .csv → Combine. Remove Other Columns (the 7 fields). Trim StoreID. Types with locale en-IN (OrderDate date; Qty whole; UnitPrice, Discount decimal). Replace null Discount → 0. Add Revenue = [Qty] * [UnitPrice] * (1 - [Discount]). Load: Connection Only.
3. dim_Stores, dim_Products, fact_Targets from the three CSVs (trim, types; Targets Month as date).
4. fact_Sales = Reference of stg_Sales → Add to Data Model.
5. chk_UnknownStores: Left Anti merge Sales → Stores. Must return 0 rows.

✅ Check: fact_Sales = 50,000 rows; StoreID has 8 distinct values.

3. Step 2 — Data model and measures (Module 4) · ~10 min

• Relationships: fact_Sales → dim_Stores (StoreID), fact_Sales → dim_Products (ProductID), fact_Sales → Calendar (OrderDate → Date).
• Calendar with FY and FY month number; mark as Date Table.
• Targets: relate fact_Targets[Region] via a small dim_Region table (North/South/East/West) and fact_Targets[Month] → Calendar[Date] — or keep targets in a separate pivot with SUMIFS if you prefer simplicity.
• Measures:

Total Revenue := SUM(fact_Sales[Revenue])
Total Cost    := SUMX(fact_Sales, fact_Sales[Qty] * RELATED(dim_Products[UnitCost]))
Profit        := [Total Revenue] - [Total Cost]
Margin %      := DIVIDE([Profit], [Total Revenue])
Orders        := DISTINCTCOUNT(fact_Sales[OrderID])
Avg Order Value := DIVIDE([Total Revenue], [Orders])
Revenue LY    := CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY %         := DIVIDE([Total Revenue] - [Revenue LY], [Revenue LY])
Revenue YTD   := TOTALYTD([Total Revenue], 'Calendar'[Date], "3/31")
Target        := SUM(fact_Targets[Target])
Achievement % := DIVIDE([Total Revenue], [Target])

(With the dim_Region approach, regional pivots must use dim_Region[Region] so both sales and targets filter. Relate dim_Stores[Region] → dim_Region too.)

The sample has 50,000 unique OrderIDs, one line per order, so COUNTROWS and DISTINCTCOUNT agree here. In real multi-line orders use DISTINCTCOUNT for Orders and AOV.

4. Step 3 — Four pivots (Module 2 + 4) · ~8 min

Use dim_Region[Region] for a Region slicer connected to all four pivots. Connect Category only to P1, P2 and P3: targets exist at Region × Month grain, so P4 must compare all-category sales with all-category targets. Keep Store/Product slicers away from P4 too. Connect the FY slicer to P1, P2 and P4; leave P3 disconnected so its two-year comparison retains both FYs. Use whole months, not individual dates, for P4 because targets are stored on month-start dates. Label these filter scopes on the dashboard.

This design follows the principle of matching comparisons to the target table’s grain. Do not allocate targets to categories without an explicit business rule.

5. Step 4 — Three charts (Module 5) · ~7 min

1. Line: monthly revenue FY2025-26 vs FY2024-25 (from P3). Title states the seasonal message.
2. Sorted bar or combo: revenue by category with Margin % labels — shows the volume vs margin trade-off.
3. Waterfall: FY2024-25 revenue → +East +North +South +West → FY2025-26 revenue (build from P1 values; set first/last as totals).

Apply the Module 5 Lesson 6 clean-up: zero-based bars, direct labels, one highlight colour, conclusion titles.

6. Step 5 — Insight summary · ~8 min

Half a page, 5–7 bullets, each with a number, a so-what and (where possible) an action. Use this structure:

1. Headline: overall growth and target result.
2. Where growth came from (regions, orders vs order value).
3. Where we're weak (region/store vs target).
4. Mix and margin (categories).
5. Seasonality and what it means for planning.
6. Recommendations (2–3, specific).
7. Data notes (cleaning done, assumptions).

7. Answer key — check your numbers

Small differences (₹1–2) from rounding are fine.

8. Example insight summary

• Revenue grew 11.0% to ₹29.50 cr, but we reached only 96.6% of target (₹1.05 cr short). The target assumed 15% growth.
• Growth came entirely from more orders (+11.1%, 23,691 → 26,309); average order value stayed flat at ₹11,213. → Test bundles/upselling to lift basket size.
• West is the problem region: slowest growth (+6.3%) and furthest from target (92.4%, ₹61 lakh short). South is the only region above target (+15.1% growth). → Review West's store-level drivers (footfall, conversion, assortment) before setting next year's target.
• Electronics is 72% of revenue but only 54% of profit (margin 12.3%), while Kitchen (28.5%) and Furniture (25.6%) earn twice the margin. → Grow Kitchen/Furniture share; watch Electronics discounting.
• Diwali months (Oct–Nov) brought 24.6% of the year's revenue, but at a 10.9% margin vs 18.4% in the other ten months, because festive discounts are deeper. → Plan stock and staffing for Oct–Nov from August; use YoY, not MoM, in monthly reviews.
• Data notes: 1,024 store codes trimmed, 239 blank discounts treated as 0, dates parsed as dd-mm-yyyy (en-IN). Targets = last year × 1.15.

9. Self-review rubric

Common mistakes

Building pivots on the raw CSV import instead of the model. Margin % as a calculated column. Comparing October with September (MoM) in a seasonal business. Insight bullets that only restate numbers without a recommendation.