Sales Dashboard Project

Load each of the three CSV files into Power Query:
  Data → Get Data → From File → From Text/CSV
  Do this three times — one query per file

For the Sales query:
  Rename the query: Sales
  Verify column types:
    Date       → Date
    ProductID  → Text (keep as text — it's a key, not a number to sum)
    RegionID   → Text
    Qty        → Whole Number
    Revenue    → Decimal Number
  Add a Month-Year column (for monthly trend):
    Add Column → Custom Column
    Name: MonthYear
    Formula: = Date.ToText([Date], "MMM-yyyy")
    Change type to Text
  Add a Year column:
    Add Column → Custom Column
    Formula: = Date.Year([Date])
    Change type to Whole Number

For the Products query:
  Rename: Products
  Verify: ProductID = Text, all price columns = Decimal Number
  No other changes needed

For the Regions query:
  Rename: Regions
  Verify: RegionID = Text, all columns = Text
  No other changes needed

Load all three to Data Model (not worksheet):
  Home → Close & Load To... → Only Create Connection
  Tick: Add this data to the Data Model
  Repeat for all three queries

Open Power Pivot: Power Pivot tab → Manage

Create relationships (Diagram View):
  Sales[ProductID]  →  Products[ProductID]   (many-to-one)
  Sales[RegionID]   →  Regions[RegionID]     (many-to-one)

This is a classic star schema:
  - Sales is the fact table (centre)
  - Products and Regions are dimension tables (points of the star)

DAX Measures — create these in a dedicated Measures table in Power Pivot:

Total Revenue:
  =SUM(Sales[Revenue])

Total Units:
  =SUM(Sales[Qty])

Transaction Count:
  =COUNTROWS(Sales)

Average Order Value:
  =DIVIDE([Total Revenue], [Transaction Count], 0)

Total Cost:
  =SUMX(Sales, Sales[Qty] * RELATED(Products[Cost Price]))

Gross Profit:
  =[Total Revenue] - [Total Cost]

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

Revenue vs Prior Year:
  =CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(Sales[Date])
  )

YoY Growth %:
  =DIVIDE(
    [Total Revenue] - [Revenue vs Prior Year],
    [Revenue vs Prior Year],
    0
  )

PivotTable 1 — Monthly Revenue Trend (feeds line chart)
  Insert → PivotTable → From Data Model
  Rows: Sales[MonthYear]  (sorted by date, not alphabetically)
  Values: [Total Revenue], [Revenue vs Prior Year]
  Name: PT_Monthly

PivotTable 2 — Revenue by Region (feeds bar chart)
  Rows: Regions[Region]
  Values: [Total Revenue], [Gross Margin %]
  Name: PT_Region

PivotTable 3 — Top 10 Products (feeds bar chart)
  Rows: Products[Product Name]
  Values: [Total Revenue]
  Sort: Total Revenue descending
  Top 10 filter: Value filters → Top 10
  Name: PT_TopProducts

PivotTable 4 — Revenue by Category (feeds donut chart)
  Rows: Products[Category]
  Values: [Total Revenue]
  Name: PT_Category

PivotTable 5 — KPI Summary (feeds KPI cards)
  No rows — Grand Total row only
  Values: [Total Revenue], [Total Units], [Transaction Count],
          [Average Order Value], [Gross Margin %], [YoY Growth %]
  Name: PT_KPI

After creating all five:
  Right-click the sheet tab → Hide
  The Helper sheet remains but is not visible to end users

Step 1: Prepare the canvas
  Insert a new sheet → rename "Dashboard"
  View → uncheck Gridlines, Headings
  Select all cells (Ctrl+A) → set row height 20, column width 3
  This creates a blank grid you can freely arrange content on

Step 2: KPI Cards — one per metric
  Select a range like B3:F8 → Merge & Centre → fill with dark colour
  In the merged cell, link to PT_KPI using GETPIVOTDATA:
    =GETPIVOTDATA("Total Revenue", Helper!$A$3)
  Format: £#,##0,,"M" to display as £2.85M
  Add a smaller merged cell below for the YoY label:
    =TEXT(GETPIVOTDATA("YoY Growth %",Helper!$A$3),"+0.0%;-0.0%") & " vs PY"
  Repeat for all four KPI cards across the top row

Step 3: Monthly Trend Chart (Line)
  Click anywhere in PT_Monthly (on Helper sheet)
  Insert → PivotChart → Line → OK
  Move chart to Dashboard sheet
  Format: remove gridlines, set line colour to #3b82f6
  Remove chart title (it's replaced by a text label above the chart area)
  Add a second line for Revenue vs Prior Year (dashed, lighter colour)
  Set chart background to match the dashboard dark theme (#1e293b)

Step 4: Revenue by Region Chart (Horizontal Bar)
  Click anywhere in PT_Region
  Insert → PivotChart → Bar → Clustered Bar → OK
  Move to Dashboard, position to the right of the trend chart
  Sort bars by descending revenue: right-click bars → Sort → Largest to Smallest
  Apply tonal colour scale: most revenue = darkest blue, least = lightest

Step 5: Top Products Chart (Horizontal Bar)
  Click anywhere in PT_TopProducts
  Insert → PivotChart → Bar → Clustered Bar → OK
  Move to Dashboard, position across the bottom row
  Show only Top 10: already filtered in PT_TopProducts

Step 6: Slicers — connect to all PivotTables
  Click any chart or PivotTable → PivotChart Analyze → Insert Slicer
  Insert slicers for: Regions[Region], Products[Category], Sales[Year]
  For each slicer → Report Connections → tick all 5 Helper PivotTables
  This makes all charts and KPI cards filter together when a slicer is clicked
  Format slicers: no header border, 1-column layout, match dark theme colours

Step 7: Final polish
  Lock the dashboard: Review → Protect Sheet
    Untick "Select locked cells" → users can only interact with slicers
  File → Save As → .xlsx (not .xlsm — no macros needed)
  Test: click each slicer, verify all 4 KPI cards and all 3 charts update

GETPIVOTDATA syntax:
  =GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)

Total Revenue KPI card (no filter — pulls grand total):
  =GETPIVOTDATA("Total Revenue", Helper!$A$3)

Gross Margin % card (formatted as percentage):
  =GETPIVOTDATA("Gross Margin %", Helper!$A$3)

YoY Growth label (positive = green, negative = red via conditional formatting):
  =GETPIVOTDATA("YoY Growth %", Helper!$A$3)
  Apply: Home → Conditional Formatting → Icon Sets → 3 Triangles
  Or: custom number format: [Green]+0.0%;[Red]-0.0%;0.0%

Why GETPIVOTDATA instead of a direct cell reference?
  A direct reference like =Helper!B2 breaks if the PivotTable layout changes
  GETPIVOTDATA always retrieves by field name regardless of where the value moves
  It also respects the current slicer filter — so as the user clicks a region,
  all KPI cards update to show that region's numbers automatically