Concept
1. Data (resources/sales)
| File | Rows | Columns |
|---|---|---|
sales_data.csv |
50,000 | OrderID, OrderDate, FY, StoreName, City, Region, Product, Category, Salesperson, Qty, UnitPrice, Discount, Revenue, Cost |
sales_targets.csv |
48 | Month, Region, Target (FY2025-26) |
Load both with Power Query (or From Text/CSV) as Tables tblSales and tblTargets on RAW sheets. Sheets: README | DASH | CALC | RAW_Sales | RAW_Targets.
2. Inputs (CALC)
| Name | Cell content |
|---|---|
lst_FY |
=SORT(UNIQUE(tblSales[FY]),,-1) |
lst_Regions |
=VSTACK("All", SORT(UNIQUE(tblSales[Region]))) |
inp_FY |
Data Validation list =lst_FY → FY2025-26 |
inp_Region |
Data Validation list =lst_Regions → All |
inp_ShowLY |
checkbox link (TRUE) |
k_Reg |
=IF(inp_Region="All","*",inp_Region) (the "All" trick) |
inp_LYFY |
="FY"&(LEFT(RIGHT(inp_FY,7),4)-1)&"-"&RIGHT(LEFT(RIGHT(inp_FY,7),4),2) → FY2024-25 |
3. KPI block (CALC)
kpi_Rev =SUMIFS(tblSales[Revenue], tblSales[FY], inp_FY, tblSales[Region], k_Reg)
kpi_RevLY =SUMIFS(tblSales[Revenue], tblSales[FY], inp_LYFY, tblSales[Region], k_Reg)
kpi_YoY =IFERROR(kpi_Rev/kpi_RevLY-1, "")
kpi_Margin =IFERROR(1 - SUMIFS(tblSales[Cost], tblSales[FY], inp_FY, tblSales[Region], k_Reg)/kpi_Rev, "")
kpi_Orders =COUNTIFS(tblSales[FY], inp_FY, tblSales[Region], k_Reg)
kpi_Target =SUMIFS(tblTargets[Target], tblTargets[Region], k_Reg, tblTargets[Month], ">="&DATE(VALUE(MID(inp_FY,3,4)),4,1), tblTargets[Month], "<"&DATE(VALUE(MID(inp_FY,3,4))+1,4,1))
kpi_Ach =IF(kpi_Target=0, "", kpi_Rev/kpi_Target)
(tblTargets holds FY2025-26 only; for multi-year targets add an FY condition.)
Cards on DASH (Module 3 Lesson 1): Revenue ₹29.50 cr ▲ 11.0% · Margin 16.5% · Orders 26,309 ▲ 11.1% · Target 96.6%.
Convert tblTargets[Month] to a real date. No target rows exist for FY2024-25: display “Target not available”, leave achievement blank, and hide the gauge. If achievement is blank, keep its Filled helper at 0; otherwise clamp it between 0 and 1.2. Never show a blank achievement as 0%. The heatmap deliberately compares all regions for the selected FY: label it “All regions — FY comparison”; the Region selector controls the other sales visuals.
4. Visual 1 — Revenue trend (CY vs LY)
Month starts in CALC!B20, CY in C20, LY in D20 — exactly the Module 4 Lesson 3 formulas with k_Reg. Line chart via names ch_Months, ch_CY, ch_LY; CY in accent, LY in grey; label only the last point.
FY2025-26 (₹ lakh): Apr 191.9 · May 204.7 · Jun 216.5 · Jul 175.5 · Aug 232.2 · Sep 247.5 · Oct 360.1 · Nov 364.8 · Dec 267.0 · Jan 221.8 · Feb 207.4 · Mar 260.7. Every month beats last year except July (175.5 vs 183.6) — worth a note on the dashboard.
5. Visual 2 — Top 5 products
The Top-N LET formula (Module 4 Lesson 3) with k_Reg and N = 5. Sorted bar chart, values in ₹ lakh.
All regions: Laptop 991.0 · Smartphone 754.8 · Smartwatch 210.8 · Earbuds 178.6 · Study Table 160.4. (West only: Laptop 240.2 · Smartphone 189.7 · Smartwatch 56.7 · Earbuds 46.9 · Study Table 41.5.)
6. Visual 3 — Region × Month heatmap
Regions down (A40 =SORT(UNIQUE(tblSales[Region]))), months across (B39 =TOROW(B20#)), one formula fills the 4 × 12 grid:
=SUMIFS(tblSales[Revenue], tblSales[Region], A40#, tblSales[OrderDate], ">="&B39#, tblSales[OrderDate], "<"&EDATE(B39#,1)) / 10^5
Show it on DASH with a linked picture (Module 4 Lesson 4) or reference cells; format with a white → accent 2-colour scale, numbers 0 decimals, small font. Hottest cells: North Oct 118.9 and Nov 118.8; coolest: East Jul 29.3.
7. Visual 4 — Salesperson leaderboard
=LET(sp, UNIQUE(tblSales[Salesperson]),
rev, SUMIFS(tblSales[Revenue], tblSales[Salesperson], sp, tblSales[FY], inp_FY, tblSales[Region], k_Reg),
ord, COUNTIFS(tblSales[Salesperson], sp, tblSales[FY], inp_FY, tblSales[Region], k_Reg),
st, XLOOKUP(sp, tblSales[Salesperson], tblSales[StoreName]),
t, FILTER(HSTACK(sp, st, ROUND(rev/10^5,1), ord), rev>0),
SORTBY(t, CHOOSECOLS(t,3), -1))
Display top 5 and bottom 3 (TAKE(x,5), TAKE(x,-3)) with rank (SEQUENCE) and data bars on revenue.
All regions top 5 (₹ lakh): Amit (Karol Bagh) 251.8 · Pooja (Karol Bagh) 241.9 · Rohit (Connaught Place) 228.1 · Sneha (Connaught Place) 220.9 · Anjali (Andheri) 193.8. Bottom: Ritika (Salt Lake) 120.1.
8. Visual 5 — Target vs actual gauge
A half-donut "progress gauge":
| Helper | Formula |
|---|---|
| Filled | =IF(kpi_Ach="",0,MAX(0,MIN(kpi_Ach,1.2))) |
| Empty | =1.2 - Filled |
| Hidden half | =1.2 |
Doughnut chart of these three → Format Series → Angle of first slice 270°, hole size 65% → Hidden half: No fill → Filled: accent (or green if ≥ 100%, via two series), Empty: light grey. Put a text box linked to =TEXT(kpi_Ach,"0.0%") in the centre → 96.6%, and a subtitle "₹29.50 cr of ₹30.55 cr target".
Prefer a bullet chart (Module 3 Lesson 2) if you need several regions — gauges use a lot of space for one number. Many dashboards show one gauge for the overall target and bullets for regions.
9. Layout and interactivity
┌ Title (dynamic) · Data as of 31-Mar-2026 [FY ▼] [Region ▼] [☑ LY] ┐
│ [Revenue] [Margin] [Orders] [Target gauge] │
│ [Revenue trend CY vs LY — line] │ [Top 5 products — bar] │
│ [Region × Month heatmap] │ [Leaderboard top 5 / bottom 3] │
└────────────────────────────────────────────────────────────────────────────┘
Dynamic title: ="Sales Dashboard — "&inp_FY&" | "&IF(inp_Region="All","All regions",inp_Region). Insight line: =IF(kpi_YoY="","No prior-year data","Revenue "&IF(kpi_YoY>=0,"up ","down ")&TEXT(ABS(kpi_YoY),"0.0%")&" vs LY")&" · "&IF(kpi_Ach="","Target not available",TEXT(kpi_Ach,"0.0%")&" of target").
10. Test checklist (answer key)
| Selection | Revenue | YoY | Achievement |
|---|---|---|---|
| FY2025-26, All | ₹29.50 cr | +11.0% | 96.6% |
| FY2025-26, West | ₹7.48 cr | +6.3% | 92.4% |
| FY2025-26, South | ₹7.14 cr | +15.1% | 100.1% |
| FY2024-25, All | ₹26.57 cr | (blank — no earlier data) | (targets only for FY25-26) |
Also test: switching Region updates each region-scoped visual (the labelled all-region heatmap stays unchanged); unticking LY hides the grey line; no #N/A or #SPILL! anywhere; prints on one landscape page.
Common mistakes
Hard-coded "FY2025-26" inside formulas instead of inp_FY. Leaderboard store via XLOOKUP when a salesperson works in two stores (here each works in one — check with COUNTIFS before relying on it). Gauge without the actual number written in it. Too many visuals — this brief needs exactly five.