Executive Dashboard Template

---
title: "Executive Dashboard Template"
methodology: "universal"
complexity: "advanced"
owner: "mirichard"
updated: "2026-09-19"
primary_principles: ["evidence-based-decisions", "stewardship"]
secondary_principles: ["quality-by-design"]
principle_rationale: "Defines Power BI measures, refresh behavior and access controls so executives can interpret governed data through usable dashboard pages."
---

# Power BI Executive Dashboard Template

## When to Use

- When executives need a Power BI dashboard with governed sources and access rules.
- When implementation teams must design measures, refresh, security, and report pages.

## When NOT to Use

- As an already deployed or validated Power BI application; this is an implementation specification.
- Do not report sample values, unsupported ratings, or missing data as verified performance. See [Metric without source](../../../../../docs/principles/anti-patterns.md#metric-source).

## Pairs Well With

- [Budget Dashboard Template](../../../../../domains/stakeholder/business-stakeholder-suite/financial-governance/budget-dashboard-template.md).
- [Executive Summary Template](../../../../../domains/measurement/project-assessment-suite/executive-summary-template.md).

Selection context: Stakeholder domain; universal methodology; advanced complexity. Match the situations above to project phase, risk, team size, and industry using the [decision-engine context model](../../../../../meta/architecture-research/800-801-context-assessment-model.md).

Define model relationships and reporting-period filters before using the sample DAX. The schema blocks are conceptual, not executable DDL. Refresh frequency depends on the chosen connection mode; “real-time” is a requirement to configure and verify, not a capability delivered by this Markdown template.


## 📊 Overview
This template provides a comprehensive Power BI dashboard designed specifically for executive-level project management insights. It includes real-time KPI tracking, portfolio performance analysis, and strategic alignment monitoring.

## 🎯 Executive Features
- **Real-time Performance Metrics**: Live project health, budget variance, timeline adherence
- **Portfolio Overview**: Multi-project performance at a glance
- **Strategic Alignment**: Goal tracking and business value realization
- **Risk Management**: Early warning indicators and mitigation tracking
- **Financial Dashboard**: ROI analysis, cost performance, and forecasting

## 🏗️ Data Model Architecture

### Core Tables Required

#### 1. Projects Table
```sql
Projects (
    ProjectID (Primary Key),
    ProjectName,
    ProjectManager,
    StartDate,
    EndDate,
    PlannedEndDate,
    Status,
    Priority,
    Portfolio,
    Budget,
    ActualCost,
    PercentComplete,
    RiskLevel,
    StrategicGoalID,
    LastUpdated
)
```

#### 2. Tasks/Activities Table
```sql
Tasks (
    TaskID (Primary Key),
    ProjectID (Foreign Key),
    TaskName,
    StartDate,
    EndDate,
    PlannedEndDate,
    Status,
    AssignedTo,
    PercentComplete,
    EstimatedHours,
    ActualHours,
    Priority,
    Dependencies
)
```

#### 3. Financial Performance Table
```sql
FinancialMetrics (
    RecordID (Primary Key),
    ProjectID (Foreign Key),
    Date,
    BudgetAllocated,
    ActualSpend,
    ForecastCost,
    ROI,
    NPV,
    IRR,
    CostPerformanceIndex,
    EarnedValue
)
```

#### 4. Risk Register Table
```sql
RiskRegister (
    RiskID (Primary Key),
    ProjectID (Foreign Key),
    RiskDescription,
    Impact,
    Probability,
    RiskScore,
    Status,
    Owner,
    MitigationPlan,
    DateIdentified,
    DateClosed
)
```

#### 5. Strategic Goals Table
```sql
StrategicGoals (
    GoalID (Primary Key),
    GoalName,
    Description,
    TargetDate,
    Status,
    KPI,
    CurrentValue,
    TargetValue,
    BusinessUnit
)
```

#### 6. Resource Utilization Table
```sql
Resources (
    ResourceID (Primary Key),
    ProjectID (Foreign Key),
    ResourceName,
    Role,
    Allocation,
    Cost,
    StartDate,
    EndDate,
    PlannedEndDate,
    Utilization,
    SkillLevel
)
```

## 📈 Dashboard Pages Structure

### Page 1: Executive Summary
**Purpose**: High-level overview for C-suite executives

**Key Visuals**:
- Portfolio Health Scorecard (Card visuals)
- Project Status Distribution (Donut chart)
- Budget Performance vs Plan (Gauge chart)
- Timeline Adherence (KPI indicator)
- Top 5 At-Risk Projects (Table)
- Monthly Trend Analysis (Line chart)

**Key Metrics**:
```dax
// Portfolio Health Score
Portfolio Health = 
AVERAGE(
    SWITCH(
        Projects[Status],
        "On Track", 100,
        "At Risk", 60,
        "Behind Schedule", 30,
        "Critical", 10,
        50
    )
)

// Budget Variance Percentage
Budget Variance % = 
DIVIDE(
    SUM(FinancialMetrics[ActualSpend]) - SUM(FinancialMetrics[BudgetAllocated]),
    SUM(FinancialMetrics[BudgetAllocated])
) * 100

// On-Time Delivery Rate
On-Time Delivery Rate = 
DIVIDE(
    COUNTROWS(
        FILTER(Projects, Projects[Status] = "Completed" && Projects[EndDate] <= Projects[PlannedEndDate])
    ),
    COUNTROWS(FILTER(Projects, Projects[Status] = "Completed"))
) * 100
```

### Page 2: Financial Performance
**Purpose**: Detailed financial analysis and forecasting

**Key Visuals**:
- ROI by Portfolio (Clustered bar chart)
- Cost Performance Index Trend (Line chart)
- Budget vs Actual vs Forecast (Waterfall chart)
- Cash Flow Analysis (Area chart)
- Top Cost Drivers (Treemap)
- Financial Forecast Accuracy (Scatter plot)

**Key Metrics**:
```dax
// Weighted Average ROI
Weighted ROI = 
// FinancialMetrics must be filtered to one reporting period.
// ROI is stored consistently as a decimal return for that period.
DIVIDE(
    SUMX(FinancialMetrics, FinancialMetrics[BudgetAllocated] * FinancialMetrics[ROI]),
    SUM(FinancialMetrics[BudgetAllocated])
)

// Cost Performance Index (CPI)
Cost Performance Index = 
DIVIDE(
    SUM(FinancialMetrics[EarnedValue]),
    SUM(FinancialMetrics[ActualSpend])
)

// Forecast Accuracy
Forecast Accuracy = 
1 - ABS(
    DIVIDE(
        SUM(FinancialMetrics[ForecastCost]) - SUM(FinancialMetrics[ActualSpend]),
        SUM(FinancialMetrics[ActualSpend])
    )
)
```

### Page 3: Portfolio Performance
**Purpose**: Multi-project performance comparison

**Key Visuals**:
- Portfolio Scatter Plot (Risk vs ROI)
- Project Timeline Gantt Chart
- Resource Utilization Heat Map
- Performance Radar Chart by Portfolio
- Project Count by Status (Stacked column)
- Milestone Achievement Rate (KPI)

**Key Metrics**:
```dax
// Portfolio Risk Score
Portfolio Risk Score = 
AVERAGE(
    RiskRegister[Impact] * RiskRegister[Probability]
)

// Resource Utilization Rate
Resource Utilization = 
DIVIDE(
    SUM(Resources[Allocation]),
    COUNT(Resources[ResourceID])
) * 100

// Milestone Achievement Rate
Milestone Achievement = 
DIVIDE(
    COUNTROWS(FILTER(Tasks, Tasks[Status] = "Completed")),
    COUNTROWS(Tasks)
) * 100
```

### Page 4: Strategic Alignment
**Purpose**: Strategic goal tracking and business value

**Key Visuals**:
- Strategic Goal Progress (Progress bars)
- Value Realization Timeline (Gantt)
- Business Impact Matrix (Scatter plot)
- Goal Achievement Forecast (Line chart)
- Strategic Initiatives Health (Scorecard)
- Benefit Realization Tracking (Waterfall)

**Key Metrics**:
```dax
// Strategic Alignment Score
Strategic Alignment = 
DIVIDE(
    COUNTROWS(
        FILTER(Projects, NOT(ISBLANK(Projects[StrategicGoalID])))
    ),
    COUNTROWS(Projects)
) * 100

// Goal Achievement Progress
Goal Progress = 
AVERAGE(
    DIVIDE(
        StrategicGoals[CurrentValue],
        StrategicGoals[TargetValue]
    )
) * 100
```

### Page 5: Risk & Issues Dashboard
**Purpose**: Risk monitoring and issue management

**Key Visuals**:
- Risk Heat Map (Matrix)
- Risk Trend Analysis (Line chart)
- Issue Aging Analysis (Column chart)
- Risk Response Effectiveness (Funnel)
- Critical Risks by Portfolio (Table)
- Risk Mitigation Timeline (Timeline)

**Key Metrics**:
```dax
// Critical Risk Count
Critical Risks = 
COUNTROWS(
    FILTER(RiskRegister, RiskRegister[RiskScore] >= 15)
)

// Risk Mitigation Rate
Risk Mitigation Rate = 
DIVIDE(
    COUNTROWS(FILTER(RiskRegister, RiskRegister[Status] = "Closed")),
    COUNTROWS(RiskRegister)
) * 100
```

## 🔌 Data Source Connections

### 1. SQL Server Connection
```powerquery
let
    Source = Sql.Database("your-server.database.windows.net", "ProjectManagementDB"),
    Projects = Source{[Schema="dbo",Item="Projects"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Projects,{
        {"StartDate", type date}, 
        {"EndDate", type date}, 
        {"LastUpdated", type datetime}
    })
in
    #"Changed Type"
```

### 2. SharePoint Lists Connection
```powerquery
let
    Source = SharePoint.Tables("https://yourcompany.sharepoint.com/sites/PMO"),
    ProjectList = Source{[Name="Projects"]}[Data],
    #"Expanded Columns" = Table.ExpandRecordColumn(ProjectList, "Fields", 
        {"Title", "Status", "StartDate", "EndDate", "Budget"})
in
    #"Expanded Columns"
```

### 3. Excel Workbook Connection
```powerquery
let
    Source = Excel.Workbook(File.Contents("C:\Data\ProjectData.xlsx"), null, true),
    Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"
```

### 4. REST API Connection
```powerquery
let
    Source = Json.Document(Web.Contents("https://api.yourpmtool.com/projects", [
        Headers=[
            #"Authorization"="Bearer " & APIToken,
            #"Content-Type"="application/json"
        ]
    ])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value")
in
    #"Expanded Value"
```

## 🎨 Visual Customization

### Color Palette (Executive Theme)
```json
{
    "primary": "#1f4e79",
    "secondary": "#2e75b6", 
    "accent": "#ff6b35",
    "success": "#28a745",
    "warning": "#ffc107",
    "danger": "#dc3545",
    "neutral": "#6c757d"
}
```

### Custom Visuals Recommended
1. **Gantt Chart by MAQ Software** - Project timeline visualization
2. **Hierarchy Slicer** - Portfolio and project filtering
3. **Advanced Donut Chart** - Status distributions with drill-through
4. **Timeline Storyteller** - Strategic milestone tracking
5. **KPI Indicator** - Performance metric display

## 📱 Mobile Optimization

### Mobile Layout Configuration
```json
{
    "mobileLayout": {
        "pages": [
            {
                "name": "Executive Summary",
                "visuals": [
                    {"type": "card", "size": "small", "position": "top"},
                    {"type": "chart", "size": "medium", "position": "middle"},
                    {"type": "table", "size": "large", "position": "bottom"}
                ]
            }
        ]
    }
}
```

### Responsive Design Guidelines
- **Cards**: Use for key metrics (3-4 per row on mobile)
- **Charts**: Optimize for touch interaction
- **Tables**: Enable horizontal scrolling
- **Filters**: Use dropdown slicers instead of list slicers

## ⚙️ Performance Optimization

### Data Refresh Strategy
```powerquery
// Incremental Refresh Configuration
Table.SelectRows(Projects, each [LastUpdated] >= RangeStart and [LastUpdated] < RangeEnd)
```

### Measures Optimization
```dax
-- Use variables to avoid recalculation
Total Projects = 
VAR ProjectCount = COUNTROWS(Projects)
VAR CompletedCount = COUNTROWS(FILTER(Projects, Projects[Status] = "Completed"))
RETURN
DIVIDE(CompletedCount, ProjectCount)
```

### Performance Guidelines
- **DirectQuery**: Use for real-time data requirements
- **Import Mode**: Use for historical analysis and reporting
- **Composite Models**: Combine both for optimal performance
- **Aggregations**: Pre-calculate common metrics

## 🔒 Security Configuration

### Row-Level Security (RLS)
```dax
-- Portfolio Manager can only see their portfolio
[Portfolio] = USERPRINCIPALNAME()

-- Executive can see all data
OR(
    [Portfolio] = USERPRINCIPALNAME(),
    LOOKUPVALUE(Users[Role], Users[Email], USERPRINCIPALNAME()) = "Executive"
)
```

### Workspace Security
- **Admin**: Full access to all content and settings
- **Member**: Can edit and share reports
- **Contributor**: Can edit but not share
- **Viewer**: Read-only access

## 📋 Implementation Checklist

### Pre-Implementation
- [ ] Validate data source connectivity
- [ ] Confirm required permissions
- [ ] Test data refresh schedules
- [ ] Verify mobile responsiveness

### During Implementation
- [ ] Import/create data model
- [ ] Configure relationships
- [ ] Create calculated measures
- [ ] Build dashboard pages
- [ ] Apply formatting and themes
- [ ] Configure security settings

### Post-Implementation
- [ ] Test all visuals and interactions
- [ ] Validate data accuracy
- [ ] Train end users
- [ ] Set up monitoring and alerts
- [ ] Document maintenance procedures

## 🎓 Training Resources

### Executive Training Topics
1. **Dashboard Navigation**: How to interact with visuals
2. **Key Metrics Understanding**: What each KPI means
3. **Drill-Through Functionality**: Getting detailed insights
4. **Mobile Access**: Using dashboards on mobile devices
5. **Export Options**: Sharing insights with stakeholders

### Administrator Training
1. **Data Source Management**: Connecting and refreshing data
2. **Security Configuration**: Setting up RLS and permissions
3. **Performance Monitoring**: Optimizing dashboard performance
4. **Troubleshooting**: Common issues and resolutions
5. **Advanced Features**: Custom visuals and DAX formulas

## 📞 Support Information

### Technical Support
- **Documentation**: Internal Power BI wiki
- **Training Videos**: Executive dashboard tutorials
- **Help Desk**: IT support ticket system
- **Power BI Community**: Microsoft community forums

### Business Support
- **PMO Team**: Business requirements and KPI definitions
- **Finance Team**: Financial metrics validation
- **Executive Sponsors**: Strategic alignment confirmation
- **Change Management**: User adoption support

---

**Template Version**: 1.0  
**Last Updated**: August 3, 2025\
**Compatibility**: Power BI Desktop 2.120+, Power BI Service\
**Created By**: Enterprise Executive Dashboard Suite - Issue #327