Concept
1. Anatomy of a KPI card
┌──────────────────────────┐
│ REVENUE · FY2025-26 │ ← label (small, grey)
│ ₹29.50 cr │ ← value (big, bold)
│ ▲ 11.0% vs last year │ ← delta (green/red)
│ ▁▂▂▁▃▃▇▇▄▃▂▄ │ ← trend (sparkline)
└──────────────────────────┘
Four parts, always in the same order on every card.
2. The KPI block in CALC
| B | C | D | E | F | |
|---|---|---|---|---|---|
| 4 | KPI | Current | Comparison | Delta | Higher is better? |
| 5 | Revenue | =SUMIFS(tblSales[Revenue],tblSales[FY],inp_FY) |
LY revenue | =C5/D5-1 |
TRUE |
| 6 | Margin % | profit ÷ revenue | LY margin | =C6-D6 (points) |
TRUE |
| 7 | Orders | =COUNTIFS(tblSales[FY],inp_FY) |
LY orders | =C7/D7-1 |
TRUE |
| 8 | Target achievement | revenue ÷ target | 100% | =C8-D8 |
TRUE |
Capstone values for FY2025-26: Revenue ₹29,49,96,410 vs ₹26,56,58,820 (+11.0%); Margin 16.5% vs 16.8% (−0.2 pts); Orders 26,309 vs 23,691 (+11.1%); Achievement 96.6%.
Name the results: kpi_Revenue = C5, kpi_RevenueDelta = E5, and so on.
3. Display text with formulas
On DASH, each card is a small block of cells (e.g. 12 columns × 4 rows of the grid):
Value:
=TEXT(kpi_Revenue/10^7, "₹0.00") & " cr"
→ ₹29.50 cr (29,49,96,410 ÷ 1,00,00,000). Always cross-check a card against the raw total once — a lakh/crore slip makes the number 100× wrong. For lakh, divide by 10^5.
Delta line:
=IF(kpi_RevenueDelta >= 0, "▲ ", "▼ ") & TEXT(ABS(kpi_RevenueDelta), "0.0%") & " vs last year"
→ ▲ 11.0% vs last year. For a points delta (margin): TEXT(ABS(x)*100,"0.0")&" pts".
Colour: conditional formatting on the delta cell — formula rule =kpi_RevenueDelta>=0 → green font; =kpi_RevenueDelta<0 → red font. For "lower is better" KPIs (costs), flip the rule using the column F flag.
4. Trend
Under the delta, a sparkline (Insert → Sparklines → Line) using a 12-month row in CALC. Show the last point marker, keep it in a light grey with the accent for the last point (Lesson 2).
5. Formatting the card
- Use Center Across Selection (Format Cells → Alignment → Horizontal) instead of Merge — sorting and copying still work.
- Background: light fill on the card cells, or a rounded rectangle shape sent to back (Lesson 5).
- Fonts: label 9–10 pt grey caps; value 24 pt bold; delta 10 pt.
- Same size and style for all cards — copy the first card's cells and only change the references.
6. Keep the numbers honest
- State the comparison explicitly ("vs last year", "vs target") — a bare ▲ is ambiguous.
- Same period comparisons only (YTD vs YTD LY, not YTD vs full LY).
- Round on the card, show exact values in a detail table or tooltip-like note.
Common mistakes
Unit errors (lakh vs crore — always cross-check). Delta without saying what it's compared to. Green for a cost increase. Merged cells that break copying.