Dashboard Design — Financial Performance Analysis¶

Dashboard Purpose¶

A Power BI financial dashboard for the CFO and FP&A team. Three pages: - Page 1 — P&L Overview: the board-ready monthly P&L - Page 2 — Profitability Analysis: segment, product, and country margins - Page 3 — Budget vs Actual: variance tracking and forecast

Page 1 — P&L Overview¶

┌──────────────────────────────────────────────────────────────────┐
│  Revenue     Gross Profit   Margin %    Discounts    YoY Growth    │
│  $118.7M     $16.9M         14.2%       $8.8M        ▲ 12%        │
├──────────────────────────────────────────────────────────────────┤
│  Revenue & Profit Trend (combo: revenue bars + margin % line)      │
├────────────────────────────────┬───────────────────────────────────┤
│  P&L Waterfall                  │  Profit by Segment (bar)          │
│  Gross→Discount→Net→COGS→Profit│                                   │
└────────────────────────────────┴───────────────────────────────────┘

Page 2 — Profitability Analysis¶

┌──────────────────────────────────────────────────────────────────┐
│  Revenue Share vs Profit Share by Segment (the key comparison)     │
├────────────────────────────────┬───────────────────────────────────┤
│  Product Margin Scatter          │  Country Profitability (map)     │
│  (revenue vs margin %)           │                                   │
├────────────────────────────────┴───────────────────────────────────┤
│  Discount Band Impact on Margin (column)                            │
└──────────────────────────────────────────────────────────────────┘

Page 3 — Budget vs Actual¶

┌──────────────────────────────────────────────────────────────────┐
│  YTD Actual: $98M   YTD Budget: $95M   Variance: +$3M (Favourable)│
├──────────────────────────────────────────────────────────────────┤
│  Actual vs Budget by Month (line, two series)                      │
├────────────────────────────────┬───────────────────────────────────┤
│  Variance by Segment (bar,      │  Year-End Forecast vs Target     │
│   red=unfavourable)             │  (gauge or bullet)                │
└────────────────────────────────┴───────────────────────────────────┘

Building in Power BI Desktop¶

Step 1 — Load and Date Table¶

DateTable = CALENDAR(MIN(financials[date]), MAX(financials[date]))

Mark as date table, relate to financials[date].

Step 2 — Core Financial Measures¶

Revenue = SUM(financials[sales])
Gross Profit = SUM(financials[profit])
COGS = SUM(financials[cogs])
Total Discounts = SUM(financials[discounts])

Gross Margin % = DIVIDE([Gross Profit], [Revenue], 0)
Discount Rate % = DIVIDE([Total Discounts], SUM(financials[gross_sales]), 0)

-- Time intelligence
Revenue YTD = TOTALYTD([Revenue], DateTable[Date])
Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(DateTable[Date]))
Revenue YoY % = DIVIDE([Revenue] - [Revenue PY], [Revenue PY], 0)

Revenue PM = CALCULATE([Revenue], PREVIOUSMONTH(DateTable[Date]))
Revenue MoM % = DIVIDE([Revenue] - [Revenue PM], [Revenue PM], 0)

Step 3 — Budget Variance Measures¶

-- Assumes a Budget table related by year_month and segment
Budget = SUM(Budget[budget_sales])
Variance = [Revenue] - [Budget]
Variance % = DIVIDE([Variance], [Budget], 0)

-- Color logic for conditional formatting
Variance Color = IF([Variance] >= 0, "#0D9488", "#EF4444")

Step 4 — P&L Waterfall¶

Visual: Waterfall chart
Category: a manually created P&L step field (Gross Sales, less Discounts, Net Sales, less COGS, Gross Profit)
This shows how revenue flows down to profit — finance executives love this view

Two bars per segment side by side: one for revenue share %, one for profit share %
This instantly reveals segments that are "revenue-heavy, profit-light"

Step 6 — KPI Cards with Conditional Formatting¶

Margin %: green if above target, red if below
Variance: green if favourable, red if unfavourable
Use the trend callout to show YoY direction

Building in Excel (Alternative)¶

Finance teams often want Excel. Build: 1. A PivotTable-based P&L with months across columns 2. A variance column (Actual − Budget) with conditional formatting (red/green) 3. A waterfall chart (Excel 2016+ has a native Waterfall chart type) 4. Slicers for Segment and Country

Design Principles for Financial Dashboards¶

Numbers must tie out — the dashboard total must match the audited P&L exactly
Show profit alongside revenue — never revenue alone
Red = unfavourable, green = favourable — the universal finance convention
Include variance, not just actuals — context is everything in finance
Default to the current fiscal period — but allow drill into history

← SQL Analysis · Next: Insights and Recommendations →