The Multi-Project Steering Challenge
A Geneva civil engineering firm simultaneously managing forty construction projects (residential buildings, public infrastructure, renovations) experienced great difficulty having a consolidated view of its portfolio. Each project manager used Microsoft Project to plan and track their project, but these files remained isolated on individual workstations. Management had to organize time-consuming weekly reporting sessions where each manager manually presented their project progress.
This situation created several problems. Management had no real-time visibility on struggling projects, often discovering schedule or budget overruns too late. Resources (engineers, draftsmen) were sometimes allocated sub-optimally because no one had an overview of the workload. Clients requesting progress updates had to wait for the project manager to manually compile information. Finally, weekly reporting sessions mobilized the entire management team for three hours.
The technical director sought a solution to centralize and automatically visualize data from all projects, offering real-time visibility without adding to project managers' workload.
The Solution Architecture
We developed an integrated ecosystem combining Microsoft Project, SharePoint, Power Automate, and Power BI. The architecture respects project managers' work habits while automating data consolidation.
Each project has a standardized SharePoint team site serving as a central hub. The Microsoft Project planning file is stored in the site's document library and synchronized with Project for the web, enabling modern collaboration. Project managers continue using Microsoft Project Desktop for detailed planning, but the file is now centralized and accessible online.
A Power Automate flow triggers daily to extract key data from each project: overall progress percentage, critical tasks, upcoming milestones within 30 days, budget overruns, resource allocation, and risk indicators (accumulated delays, overdue tasks, budget consumed vs actual progress). This data is consolidated in a central SharePoint list serving as the source for Power BI.
In addition, a second flow monitors activity in each project's Teams channels. It analyzes conversations to detect messages containing keywords signaling potential problems (delay, problem, blocked, urgent, unhappy client) and generates alerts for management. This simple but effective text analysis enables proactive identification of projects requiring particular attention.
A mobile Power Apps form allows project managers to quickly enter weekly status updates: qualitative progress status (green/orange/red), main accomplishments of the week, obstacles encountered, assistance needs, and next steps. This information enriches the central database.
The system's core is a set of Power BI dashboards offering different views according to needs. The management dashboard presents a portfolio view of all projects with their status, progress percentage, budget and schedule deviations, plus aggregated indicators (number of delayed projects, total budget consumed, team workload). Filters enable segmentation by project type, client, project manager, or phase.
The resource dashboard displays each employee's workload distributed across projects, identifying overloaded or underutilized people. This view facilitates allocation optimization and anticipation of recruitment or subcontracting needs.
The client dashboard, accessible via a secure link, presents a summary view of a specific project with progress of different phases, achieved and upcoming milestones, delivered outputs, and a photo gallery of the worksite automatically updated from Teams. This transparency is highly appreciated by project owners.
Finally, a forecasting dashboard uses historical data to project probable completion dates for each project and identify budget overrun risks. This simple but useful predictive module helps management anticipate difficulties.
Measured Benefits
After seven months of use, impacts are significant. Weekly reporting sessions were reduced from three hours to one hour, with most information now accessible self-service via Power BI. Discussions focus on strategic decisions and helping struggling projects rather than information gathering.
Real-time visibility enabled faster problem identification and resolution. Average detection delay for deviations dropped from about two weeks to three days, allowing early corrective actions. The number of projects finishing with over 10% budget overrun decreased 60%.
Resource optimization improved. The consolidated workload view enables better work balance among staff and anticipation of overload periods. Engineers' billable utilization rate increased by 8 percentage points through better allocation.
Clients greatly appreciate the personalized dashboards. The number of ad-hoc reporting requests dropped 75%, with clients finding needed information directly in their dashboard. This transparency contributed to improving client relations and facilitated renewal of several framework contracts.
Management now has objective data for strategic decisions: accepting new assignments, human resource investments, or identifying the most profitable activity domains. Management meetings rely on measurable facts rather than impressions.
Adoption and Change Management
Introducing the system required change management. Some project managers initially feared excessive surveillance or additional workload. We organized workshops explaining that the goal was decision support and information sharing, not control.
Managers quickly found that the system also saved them time: no more preparing presentations for reviews, client reports are generated automatically, and they themselves benefit from better visibility of their teams' workload. After three months, adoption rate reached 95%.
A change champion was designated in each department to help colleagues and raise improvement suggestions. The system evolved based on this user feedback, reinforcing ownership.
Technical Architecture and Performance
The system relies on a Power BI data model connected to several sources: SharePoint lists for consolidated data, Microsoft Project API for detailed schedules, and Microsoft Graph for Teams data. Data refreshes are scheduled four times daily, offering near real-time visibility without overloading systems.
Power BI dashboards are optimized for good performance despite data volume (40 projects × 100 to 500 tasks). Use of aggregated tables and efficient DAX measures guarantees loading times under 3 seconds.
Monthly cost including Power BI Pro licenses for 15 users, Power Automate Premium for advanced flows, and houle support represents approximately 400 CHF. The three weekly meeting hours saved for 8 participants (24 person-hours per week) alone generate positive ROI, not counting indirect benefits in management quality.
Future Evolutions
The engineering firm plans several enhancements. Integration with the billing system would display real project margins in real-time in Power BI. Connection with document management tools could automatically track production of contractual deliverables. Finally, more sophisticated predictive analysis using Power BI machine learning could improve deadline and cost forecasts.
Conclusion
This project portfolio steering system demonstrates how Power BI can transform management of a multi-project organization. By automatically aggregating data scattered across different Microsoft tools, we create a unified vision benefiting all organizational levels. The engineering firm now has a true steering cockpit offering transparency, responsiveness, and decision support—major assets in a competitive sector where deadline and budget mastery make the difference.