Dashboards, KPIs and Power BI
Last reviewed 9 September 20262,506 words11 min read
Twelve numbers from the schedule, refreshed at the data date — and nothing the schedule does not already know
🟩 In one line: A planning dashboard shows a fixed set of KPIs read directly from the rescheduled update — variance, movement, CPLI, BEI, SPI, productivity factor, PPC, near-critical count, open constraints, procurement float, notices, EV percent — with thresholds fixed in the SBM; it refreshes once per data date from an archived export, and it never calculates a date or a float that P6 did not.
👤 Who this is for: Mid-level planners asked for "a dashboard"; senior planners setting up a PMO reporting model. Prerequisites: Slippage-and-trend-analysis, Cpli-and-bei, Earned-value-basics-spi-cpi, Reading-an-xer-in-excel.
First, let's be honest about why this page exists
Dashboards fail in one of two ways. The first is decoration: a Power BI page of donuts and speedometers built by someone who never opened the schedule, coloured green because activity count complete is 61 %. The second is duplication: the dashboard recalculates float from start and finish dates in DAX, disagrees with P6 by four days, and the meeting spends forty minutes on which number is right.
The schedule already computes everything worth showing. The dashboard's job is to read it, trend it and threshold it — once a month, from the same file the Engineer received — so that the PMO and the project director see the same twelve numbers the narrative states, one screen at a time.
🟨 The standard — what "good" looks like
| Source | What it says (paraphrased) | Use it for |
|---|---|---|
| DCMA 14-Point Assessment, items 13 and 14 | CPLI and BEI defined with thresholds (0.95); computed against the baseline | Two of the twelve, with their thresholds |
| GAO Schedule Assessment Guide, Best Practices 8 and 9 | Progress updates traceable; schedule and cost data integrated and consistent | Dashboard data must trace to the update |
| PMI Standard for Earned Value Management (2019) | SPI, CPI, SV, CV definitions (check against your copy) | EV KPIs |
| AACE RP 38R-06 | Reporting metrics, thresholds and their basis documented in the SBM | KPI definitions live in the SBM, not in the .pbix |
| FIDIC 1999 Cl 4.21 / 2017 Cl 4.20 | Progress report compares planned and actual progress | Dashboard is an internal view of the same numbers |
| Hub convention | Trend register one row per DD, never reset; thresholds CPLI/BEI/SPI 0.95; PPC <60 % fiction, >85 % working; near-critical 10–20 wd buildings; slippage ratio 0.3 modest / 1.0 standstill; PF earned ÷ actual; dashboard refresh at DD from archived export | Your KPIs and thresholds |
🟢 Rule: every dashboard number has a source field in P6 or a register, a threshold in the SBM, and a matching value on page one of the monthly report — if the three disagree, the dashboard is wrong.
How it actually works
1. The twelve KPIs. Fixed at baseline, in the SBM, with source and threshold. Add a thirteenth only with a baseline revision.
| # | KPI | Definition | Source | Green | Amber | Red |
|---|---|---|---|---|---|---|
| 1 | Variance to contract (wd) | Forecast contractual completion − contract date, working days, per Section | Layout 04 / trend register | ≥ 0 | −1 to −10 | < −10 |
| 2 | Movement (wd) | Forecast this DD − forecast previous DD | Trend register | ≥ 0 | −1 to −5 | < −5 |
| 3 | Slippage ratio | Movement ÷ working days in period | Trend register | < 0.3 | 0.3–1.0 | > 1.0 |
| 4 | CPLI | (CPL + TF) ÷ CPL to contractual completion | P6 fields | ≥ 1.00 | 0.95–0.99 | < 0.95 |
| 5 | BEI | Activities finished ÷ baseline finish ≤ DD (excl. LOE/WBS Summary) | P6 export | ≥ 0.95 | 0.90–0.94 | < 0.90 |
| 6 | SPI (hours) | EV ÷ PV labour units | P6 EV | ≥ 0.95 | 0.90–0.94 | < 0.90 |
| 7 | Planned vs earned % | PV ÷ BAC and EV ÷ BAC | P6 EV | gap ≤ 3 pts | 3–6 | > 6 |
| 8 | Productivity factor (period) | Earned mh ÷ actual mh, last 4–6 wk, by trade | Productivity workbook | ≥ 0.95 | 0.85–0.94 | < 0.85 |
| 9 | PPC (4-wk avg) | Committed complete ÷ committed | Weekly workbook | > 85 % | 60–85 % | < 60 % |
| 10 | Near-critical count | Activities with 0 < TF ≤ threshold, not LOE | Layout 04 | ≤ 5 % of remaining | 5–10 % | > 10 % |
| 11 | Open constraints / interfaces overdue | Count with clear-by < DD | Constraint & interface registers | 0 | 1–5 | > 5 |
| 12 | Procurement items with negative float | Count from long-lead register | Procurement register | 0 | 1–2 | ≥ 3 |
Notices issued / open and EOT days claimed / awarded appear as counts beside KPI 1 without a colour — they are facts, not performance.
2. Getting the data out of P6. Three routes, in order of preference:
| Route | How | Use | Caution |
|---|---|---|---|
| Excel export from layouts | File → Export → Spreadsheet (XLS/XLSX): activities with the columns you need, plus resource assignments (check against your P6 version) | Default; one export per DD, archived with the XER | Export exactly the columns the model expects; save the export template |
| XER parsed | The archived XER read in Excel/Power Query by table (TASK, PROJWBS, TASKPRED, TASKRSRC, ACTVCODE, UDFVALUE) — see Reading-an-xer-in-excel | When the model needs relationships or codes not in a layout | Hours are stored as hours; durations in hours ÷ hours/day of the activity's calendar |
| Read-only database connection | ODBC to the P6 database via a reporting user with read-only rights, or the Oracle Primavera Cloud / EPPM reporting views | Enterprise PMO, many projects | Admin creates the user; never the planner's login; still snapshot at DD — a live connection shows a half-updated schedule |
Whichever route: one refresh per data date, from the archived export of the accepted update, tagged with Update number and DD. Weekly data (PPC, PF) refreshes weekly from the workbooks and is labelled as such.
3. The data model. Five tables and a date table. Keep it that small.
| Table | Grain | Key fields |
|---|---|---|
| Activities | One row per activity per update | Update ID, DD, Activity ID, WBS, OD, RD, Start, Finish, Actual Start/Finish, TF, Physical %, Longest Path flag, activity type, BL Project Start/Finish, codes (Area, Level, Discipline, Subcontractor, Responsibility, Milestone Type), Budgeted/Actual/Remaining Units, PV/EV |
| Trend | One row per DD per Section | All trend-register columns |
| Registers | One row per item per update | Constraints, interfaces, procurement, notices, changes — with status and clear-by |
| Weekly | One row per week | PPC, reasons by category, headcount by trade |
| Productivity | One row per trade per week per area | Earned, actual, PF, impact tag |
| Date | One row per day | Working-day flag per project calendar (from the calendar list in the SBM) |
Relationships: Activities → Date on DD; everything → Update ID. Do not build a relationship table for logic in the dashboard; the critical path is a flag from P6, not something the dashboard derives.
4. The measures — what the dashboard may and may not compute.
| May compute | Must read from P6 / register |
|---|---|
| Counts, sums, ratios of exported fields (BEI, near-critical count, PV/EV ratios) | Total float, longest path, early/late dates, forecast completion |
| Period-over-period differences (movement, slippage ratio) from the trend table | CPLI inputs (CPL and TF at the milestone) |
| Threshold colouring | Anything involving calendars or relationships |
| Trend lines and averages | EV technique and baseline selection |
Working days between two dates come from the Date table's working-day flag for the relevant calendar, never from a 5-day network function.
5. The pages. Four, in reading order, matching the monthly report.
| Page | Content |
|---|---|
| Status | Twelve KPIs with colours; contract vs forecast per Section; driver activity ID; Update and DD stamp; baseline name |
| Trend | Variance, movement, CPLI, BEI, SPI as lines over all DDs; baseline revisions as vertical markers; slippage ratio bars |
| Drill-down | Activities table filterable by Area/Level/Discipline/Subcontractor: RD, TF, variance to baseline, Physical %; near-critical list; longest-path chain in order |
| Readiness | Open constraints, interfaces overdue by party, procurement negative-float items, PPC trend, PF by trade |
Every page carries "Source: Update NN, DD dd-MMM-yy, BLn-Accepted-…, refreshed dd-MMM" in the footer.
6. Governance. Thresholds and definitions in SBM section 13; changed only with a baseline revision. The .pbix and the export files archived per DD with the XER. Access to the Engineer's-facing pages limited to the KPIs that appear in the monthly report; PPC and PF are internal. The planner owns the refresh; the PMO owns the visuals; nobody edits data in the model.
🟥 Where people go wrong
- Float recomputed in DAX. Finish minus late finish on a 5-day network function, disagreeing with P6 by the calendar difference. Float is a field read from P6, full stop.
- Percent complete by activity count. 1,600 of 2,800 done = 57 % green. LOE, milestones and 2-day activities count the same as a 40-day slab. Weight by manhours or cost, as the S-curve does.
- Live database connection during the update. The director opens the dashboard on DD + 1 while half the actuals are in and sees a 60-day slip. Snapshot at the archived export, tagged with the Update number.
- Thresholds set in the visual. Someone changes red to start at −20 to make the page calmer. Thresholds in the SBM, referenced in the model, changed only with a revision.
- Dashboard numbers differ from the report. The report used the accepted update; the dashboard was refreshed from a Reflection. One export, one source, one Update ID on both.
- Building the logic network in Power BI. Relationship tables, path tracing, "our own critical path". It is a second scheduling engine that will disagree with the first. Longest Path flag from P6.
- Thirty KPIs. Nobody reads them, and the ones that matter get lost. Twelve, fixed, each with a source and a threshold.
⚖️ When you're challenged
"The dashboard says 57 % complete and your report says 46 %. Which is it?" Forty-six. The 57 is activity count, which the dashboard shouldn't be showing — it counts a milestone the same as a slab. Earned manhours over budget is 46.3 %, same as the S-curve and the narrative; I've removed the count visual.
"Can we have the dashboard live, updated daily?" Weekly data — PPC, headcount, productivity — can refresh weekly from the workbooks. Schedule data can only be as current as the last reschedule, which is monthly. A daily feed from the database would show a half-statused file for a week every month and a different completion date every day.
"Why is CPLI amber when float is still positive?" CPLI is 0.97: four days of float on a 130-day remaining longest path, which is inside the amber band from the SBM. Positive, but thin — that's exactly what the amber is for.
"Just give the PMO a login to P6." A read-only reporting user on the database is fine if the admin sets it up; a P6 client login isn't, because one accidental F9 or baseline change in the live project can't be undone. The dashboard reads the archived export of the accepted update, which is the same thing the Engineer received.
📄 Related pages
- Slippage and Trend Analysis — the trend register the dashboard trends
- CPLI and BEI — definitions and thresholds for KPIs 4 and 5
- Earned Value Basics: SPI and CPI — KPIs 6 and 7
- Manhour Budget and Productivity Tracking — KPI 8
- Weekly Report and Look-Ahead — KPI 9 and the readiness data
- Reading an XER in Excel — parsing the XER tables the model can use
- Monthly Progress Report — page one is the same twelve numbers
- Redirect: dashboard-kpis and power-bi-and-excel-from-p6 are merged into this page
✏️ Worked example — Jeddah hospital, dashboard status page at Update 15 (DD 25-Aug)
| # | KPI | Value | Colour | Source |
|---|---|---|---|---|
| 1 | Variance — Block 2 / Whole | −18 wd / −6 wd | Red / Amber | Layout 04 |
| 2 | Movement | −4 wd / −1 wd | Amber / Amber | Trend register |
| 3 | Slippage ratio | 0.17 / 0.04 | Green | Trend register |
| 4 | CPLI (Whole) | 0.96 | Amber | P6 |
| 5 | BEI | 0.88 | Red | Export: 412 ÷ 468 |
| 6 | SPI (hours) | 0.92 | Amber | P6 EV |
| 7 | Planned / earned % | 44.4 / 41.0 (gap 3.4) | Amber | P6 EV |
| 8 | PF period — MEP / finishes / structure | 0.83 / 0.79 / 0.98 | Red / Red / Green | Productivity workbook |
| 9 | PPC (4-wk) | 68 % | Amber | Weekly workbook |
| 10 | Near-critical count | 96 of 1,410 remaining (6.8 %) | Amber | Layout 04, TF ≤ 15 |
| 11 | Overdue constraints / interfaces | 4 (2 Engineer, 1 utility, 1 MC) | Amber | Registers |
| 12 | Procurement negative float | 1 (MRI, Employer-supplied) | Amber | Long-lead register |
| — | Notices open / EOT claimed / awarded | 3 / 22 wd / 0 wd | — | Notice register |
Footer: "Source Update 15, DD 25-Aug, BL1-Accepted-DD 25Feb-Rev1; activities export Jeddah-H_Upd15_DD25Aug_v01.xlsx; weekly data to w/e 21-Aug; refreshed 28-Aug."
Read: Twelve numbers, all traceable to the report's page one and sections 4–9. The drill-down page filtered to Block 2 → MEP shows MEP-B2-L3-DUC-140 at TF −18 with Longest Path = Yes, matching the narrative's driver. The two Engineer-owned overdue constraints (RFI-244, submittal S-131) link to notices N-027 and N-028 in the readiness page.
📖 References
- DCMA 14-Point Schedule Assessment, items 13 and 14
- GAO, Schedule Assessment Guide, GAO-16-89G, Best Practices 8 and 9
- PMI, The Standard for Earned Value Management (2019) (check against your copy)
- AACE International RP 38R-06, Documenting the Schedule Basis
- FIDIC Conditions of Contract for Construction 1999, Cl 4.21; 2017, Cl 4.20 (check the edition in your contract)
- Oracle Primavera P6 Professional User Guide — Export to spreadsheet; Reporting; database users (check against your P6 version)
From the field
Experience from working planners. Unreviewed — read it as experience, not guidance.
Add what you know about dashboards, kpis and power bi. What worked, what the consultant pushed back on, what you would do differently next time. A paragraph is plenty.
Contributors get their name and one link on the site — your own templates, course or consultancy. We take nothing and hold nothing.
Add your experience