Building an Executive Power BI Dashboard for Construction Portfolio Management

Construction project portfolios generate data constantly — budgets, actual costs, schedules, subcontractor status — but that data is almost always scattered across spreadsheets, project management tools, and email threads. Executives end up making decisions based on incomplete, stale information. The goal of this project was to change that.

The Reporting Gap in Construction

Most construction companies have the data. What they lack is a consistent, trustworthy way to surface it. A portfolio manager shouldn't need to manually compile a status report every Monday morning — that process introduces errors, takes time, and produces a snapshot that's already outdated by the time it's reviewed.

The dashboard I built addresses this with two pages: an executive KPI overview and a project-level detail drilldown. The executive page gives leadership the 10,000-foot view — portfolio revenue, cost variance, and margin — while the detail page lets project managers drill into individual project health, schedule status, and cost breakdown.

Data Modeling: Why Star Schema Matters

Power BI performance lives and dies by the data model. A flat, denormalized table might seem simpler at first, but it leads to slow DAX calculations, ambiguous relationships, and reports that take seconds to filter. The star schema approach — a central fact table surrounded by dimension tables — solves all of this.

• ›Fact table: one row per project-month with budget, actual cost, and variance figures
• ›Project dimension: project name, category, region, project manager, start/end dates
• ›Time dimension: full calendar table enabling YoY, MoM, and quarter comparisons
• ›Category dimension: project type, trade, and subcontractor classification

Power Query handled the data preparation: merging budget and actuals from separate Excel tabs, standardizing column names, casting date fields, and computing derived columns like cost variance percentage and days remaining. Keeping transformation logic in Power Query (M code) rather than DAX keeps the model clean and the queries fast.

The DAX Health Scoring Model

The most technically interesting part of this project was designing the project health score — a single number from 0–100 that reflects the overall status of a project. The challenge is that "health" is multidimensional: a project can be on budget but behind schedule, or ahead of schedule with a cost overrun. A naive average of these factors obscures critical signals.

1-- Weighted DAX health score (simplified)
2Project Health Score =
3VAR CostScore =
4    SWITCH(
5        TRUE(),
6        [Cost Variance %] >= 0,          100,  -- under or on budget
7        [Cost Variance %] >= -0.05,       80,  -- within 5% over
8        [Cost Variance %] >= -0.10,       60,  -- within 10% over
9        [Cost Variance %] >= -0.20,       40,  -- within 20% over
10        20                                     -- >20% over budget
11    )
12VAR ScheduleScore =
13    SWITCH(
14        TRUE(),
15        [Days Behind Schedule] <= 0,     100,
16        [Days Behind Schedule] <= 7,      75,
17        [Days Behind Schedule] <= 14,     50,
18        25
19    )
20RETURN
21    CostScore * 0.6 + ScheduleScore * 0.4

Cost variance carries 60% of the weight because budget overruns directly impact project profitability and client contracts. Schedule score carries 40%. The resulting score populates a KPI card with conditional formatting — green above 80, amber 60–80, red below 60 — giving leadership instant visual triage across the portfolio.

Visual Design for Executive Audiences

Executive dashboards fail for the same reason most reports fail: too much information, not enough hierarchy. The rule I followed was that the most important number on each page should be readable in under two seconds. KPI cards sit at the top with large typography. Supporting charts — cost trend lines, margin by category, project health scatter — fill the lower half.

• ›KPI cards: portfolio revenue, total cost, gross margin, project count — top row
• ›Trend chart: monthly revenue and cost actuals vs. budget over rolling 12 months
• ›Category breakdown: margin by project type as a horizontal bar chart
• ›Health matrix: scatter plot of cost variance vs. schedule variance, colored by health score
• ›Slicers: region, project manager, project category — all cross-filter every visual