Full Dashboard Project

This lesson brings together everything from Section VI — dashboard design principles, charts, slicers, and PivotTables — into a single end-to-end build. You will construct a complete Sales Performance Dashboard from scratch: loading data into a Power Pivot model, building the measure library, laying out the dashboard sheet, building and formatting every chart and KPI card, adding a slicer panel, and connecting everything so that a single Region or Date filter updates the entire dashboard simultaneously. By the end, you will have a production-ready, interactive Excel dashboard to use as a template for your own projects.

Project Overview — What We Are Building

The finished dashboard has four zones: a KPI bar with four headline measures, a main trend chart, a regional breakdown panel, and a supporting details section. Three slicers — Region, Category, and a Timeline — control all visuals simultaneously. The data model underneath has three tables connected in a star schema.

Finished Dashboard — Full Layout Preview

Sales Performance Dashboard

Dataplexa · FY2025 · All figures in GBP

Last refreshed: Today

FILTERS

Region

All

Step 1 — Data and Model Setup

The dashboard uses three source tables: Sales (fact), Customers (dimension), and Products (dimension). Load all three into the Power Pivot data model, build the relationships, and create the Date Table before writing any measures.

Source data — three sheets in the workbook:

Sales (fact table):
  OrderID | CustomerID | ProductID | OrderDate | Quantity | Revenue | Cost

Customers (dimension):
  CustomerID | CustomerName | Region | Segment

Products (dimension):
  ProductID | ProductName | Category | SubCategory | UnitCost

Step 1a — Load to Power Pivot:
  Click any table → Power Pivot tab → Add to Data Model
  Repeat for all three tables

Step 1b — Create relationships in Diagram View:
  Sales[CustomerID] → Customers[CustomerID]   (Many-to-One)
  Sales[ProductID]  → Products[ProductID]     (Many-to-One)

Step 1c — Create Date Table in Power Query:
  Data → Get Data → Launch Power Query Editor
  Home → New Source → Blank Query → Advanced Editor → paste:

let
  StartDate = #date(2024, 1, 1),
  EndDate   = #date(2025, 12, 31),
  DayCount  = Duration.Days(EndDate - StartDate) + 1,
  Dates     = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
  Table     = Table.FromList(Dates, Splitter.SplitByNothing()),
  Renamed   = Table.RenameColumns(Table, {{"Column1", "Date"}}),
  Typed     = Table.TransformColumnTypes(Renamed, {{"Date", type date}}),
  AddYear   = Table.AddColumn(Typed, "Year",     each Date.Year([Date]),    Int64.Type),
  AddMonth  = Table.AddColumn(AddYear, "MonthNum", each Date.Month([Date]), Int64.Type),
  AddMName  = Table.AddColumn(AddMonth, "MonthName", each Date.ToText([Date],"MMM"), type text),
  AddQtr    = Table.AddColumn(AddMName, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text)
in AddQtr

  Rename query to "DateTable" → Load to Data Model

Step 1d — Relate and mark Date Table:
  Diagram View: DateTable[Date] → Sales[OrderDate]
  Power Pivot → Design → Mark as Date Table → Date

Step 2 — The Measure Library

Build all measures in a dedicated _Measures table. Format each measure immediately after creation so every PivotTable that uses it automatically displays correctly.

Core measures:
Total Revenue    := SUM(Sales[Revenue])
Total Cost       := SUM(Sales[Cost])
Order Count      := COUNTROWS(Sales)

Gross Profit :=
  VAR Rev  = [Total Revenue]
  VAR Cost = [Total Cost]
  RETURN Rev - Cost

Profit Margin % :=
  DIVIDE([Gross Profit], [Total Revenue], 0)

Time intelligence:
Revenue LY :=
  CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))

YoY Growth % :=
  VAR Curr = [Total Revenue]
  VAR LY   = [Revenue LY]
  RETURN DIVIDE(Curr - LY, LY, BLANK())

Revenue YTD :=
  TOTALYTD([Total Revenue], DateTable[Date])

Share and ranking:
Revenue % of Total :=
  DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(Sales)), 0)

Customer Rank :=
  RANKX(ALL(Customers[CustomerName]), [Total Revenue], , 0, Dense)

KPI status:
Revenue Target := SUM(Targets[RevenueTarget])

vs Target % :=
  DIVIDE([Total Revenue] - [Revenue Target], [Revenue Target], BLANK())

KPI Status :=
  VAR Pct = [vs Target %]
  RETURN
    IF(ISBLANK(Pct), BLANK(),
    IF(Pct >=  0.05, "On Track",
    IF(Pct >= -0.05, "At Risk",
                     "Behind")))

Step 3 — Dashboard Sheet Setup

Sheet configuration:
  Add new sheet → rename "Dashboard" → Tab Color: #7C3AED
  View → untick Gridlines and Headings
  View → Zoom → 80%
  Ctrl+A → Fill: #F1F5F9

Hidden helper sheet "Data":
  Holds GETPIVOTDATA formulas that feed the KPI cards
  =GETPIVOTDATA("Total Revenue", Dashboard!$A$1)
  Right-click tab → Hide after setup

Row/column grid:
  Row  1–2:  top padding (height 8)
  Row  3–4:  title and subtitle
  Row  5–6:  filter/slicer zone (height 42)
  Row  7:    spacer (height 6)
  Row  8–11: KPI cards (height 18 each)
  Row  12:   spacer
  Row 13–22: main chart zone
  Row 23:    spacer
  Row 24–32: detail panel zone

Sheet protection:
  Review → Protect Sheet
  Allow: Select unlocked cells, Use PivotTable reports, Use AutoFilter

Step 4 — KPI Cards

Build one card per headline measure:

Revenue card (example):
  Merge B8:D11 → Fill: #7C3AED
  Type "REVENUE" → white, 8pt, bold, uppercase
  Cell below: ='Data'!A1  (GETPIVOTDATA result → £1,620,000)
    Font: white, 22pt, bold
  Cell below that: ='Data'!A2  (trend formula → "▲ 8.4% YoY")
    Font: #C4B5FD, 8pt
  All borders: None

Trend formula on Data sheet:
  =IF(YoY_Growth>=0,
    "▲ " & TEXT(ABS(YoY_Growth),"0.0%") & " YoY",
    "▼ " & TEXT(ABS(YoY_Growth),"0.0%") & " YoY")

Repeat for:
  Gross Profit card  → Fill: #15803D
  Orders card        → Fill: #1D4ED8
  Margin % card      → Fill: #D97706

Step 5 — Charts

Chart 1 — Monthly Revenue vs Prior Year (line):
  PivotTable (hidden, Data sheet):
    Rows: DateTable[MonthName] sorted by MonthNum
    Values: Total Revenue, Revenue LY
  Insert → PivotChart → Line → move to Dashboard
  Format:
    Total Revenue: #7C3AED, 2.5pt, no markers
    Revenue LY:    #C4B5FD, 1.5pt dashed, no markers
    Gridlines: #E2E8F0, 0.5pt horizontal only
    No legend → direct endpoint labels
    Background: #FFFFFF, no border
  Hold Alt while sizing to snap to cell grid

Chart 2 — Revenue by Region (horizontal bar):
  PivotTable: Rows=Customers[Region], Values=Total Revenue
  Sort descending by Total Revenue
  Tonal purple scale, data labels inside bars (white 9pt bold)
  Delete value axis

Chart 3 — Top 5 Customers (horizontal bar):
  PivotTable: Rows=Customers[CustomerName], Values=Total Revenue
  Value Filter → Top 5 by Total Revenue
  Same formatting as Chart 2

Chart 4 — Category Mix (bar):
  PivotTable: Rows=Products[Category], Values=Revenue % of Total
  Format axis as percentage

Step 6 — Slicer Panel

Insert slicers:
  PivotTable Analyze → Insert Slicer:
    Customers[Region]   → 4 columns
    Products[Category]  → 3 columns
  PivotTable Analyze → Insert Timeline:
    DateTable[Date]     → QUARTERS mode

Connect all slicers to all PivotTables:
  Right-click each slicer → Report Connections → tick every PivotTable

Custom slicer style (duplicate built-in):
  Selected button:   Fill #7C3AED, Font white bold
  Unselected button: Fill white, Border #E2E8F0, Font #475569
  Header:            No fill, Font #64748B 8pt uppercase
  Slicer border:     None

Filter zone background:
  Insert → Shapes → Rectangle → Fill #F1F5F9 → No outline → Send to Back
  Add "FILTERS" text label: Insert → Text Box, 8pt #94A3B8 uppercase

Step 7 — Final Polish and Testing

Alignment:
  Select all charts → Drawing Tools → Align Left, Align Top
  Hold Alt while resizing to snap to cell grid

Typography standards:
  Chart titles:  10pt bold uppercase #0F172A
  Axis labels:   8pt #64748B
  Data labels:   9pt bold (match series colour or white)
  KPI labels:    8pt bold white / light tint

Functional test checklist:
  Click each Region button → all 4 KPI cards update
  Click each Region button → all 4 charts update
  Select date range on Timeline → all visuals filter
  Select a Category → charts and cards respond
  Ctrl+click two regions → combined total shown
  Click slicer Clear (X) → Grand Total restored on all visuals
  Data → Refresh All → model reloads, all visuals update
  Click a chart area → sheet protection blocks accidental edit

Final file steps:
  File → Properties → set Title and Author
  Save as .xlsx (or .xlsb for faster load on large models)
  Share the Dashboard sheet only — send the protected .xlsx

Complete Sales Performance Dashboard: 4 KPI cards, 4 charts, 3 slicers (Region + Category + Timeline), 12 measures, 3-table Power Pivot model, sheet protection — fully interactive, refreshable, and ready to share

💡 Teacher's Note

The functional test checklist in Step 7 is not optional housekeeping — it is the quality gate. Every dashboard, no matter how well built, has at least one broken connection or formatting inconsistency that only surfaces during testing. The most damaging mistake is delivering a dashboard where clicking a slicer updates some visuals but not others — users lose trust in the entire report when even one chart appears disconnected. Run the full checklist before sharing anything. It takes five minutes and saves significant credibility.

🟠 Practice

Q1. After building the dashboard, you click the "North" Region slicer and the KPI cards update but the monthly trend chart does not filter. What is the most likely cause and how do you fix it?

Q2. You want the Revenue KPI card to show the correct filtered value when a slicer is applied, but the reference must not break if the PivotTable layout changes. Which function should the helper cell use?

Q3. What is the benefit of saving the final dashboard as .xlsb rather than .xlsx for a large Power Pivot model?

Next up — Section VII: Automation and Office Scripts, where we introduce Office Scripts — Excel's modern automation language — starting with recording, reading, and editing scripts to automate repetitive tasks without leaving the browser.

← Previous Course Index Next →

Excel Course