Goal
To create an executive-level dashboard to provide a high level overview of project workload trends. This dashboard offers a strategic visualisation of the distribution of project statuses, to enable stakeholders to understand the overall progress of the portfolio at a glance.
Data Source/s
I used a data set from a schools maintenance and construction project program in New York as the base data, modified to be suitable for displaying in this dashboard format.
https://data.world/city-of-ny/2xh6-psuq/workspace/file?filename=capital-project-schedules-and-budgets-1.csv
Data Cleanse / ETL (excel, Power Query)
-
The original dataset included both Missing At Random (MAR) and Missing Completely At Random (MCAR) data types.
​
-
For demonstration purposes of this dashboard visualisation, Iin the null MCAR fields I populated with median averages, where relevant. (In a real-world scenario on discovering these type of data gaps I would work with the project team to ensure they populate with factual data, or look to demonstrate and document these gaps)
​
-
Used Excel's PROPER and TRIM functions to tidy up field entries with unnecessary whitespace and inconsistent capitalisation. With the use of Pivot tables, I ensured contractors with specialised skill sets were assigned exclusively to their appropriate disciplines.
​
-
Used the TEXT TO COLUMNS to adapt dates into a UK date format. I also transformed string fields holding numbers into numerical fields.
​
-
Replaced the existing project status indicators with the standards established by the Royal Institute of British Architects (RIBA), a more recognised project status definition within the UK.
​
-
In Power Query, I deleted unnecessary columns, added an index, and the data types (String/date/float, etc.) for each column were adjusted according to the requirements.
​
-
This was a fairly simple ETL process in power query as I had the flexibility during the data wrangling in excel to set it up in a suitable format for easy import into power query/BI
Design
-
My aim was to track workload patterns and demands on contractors, focusing especially on discipline loading and the spread of project statuses. This approach was intended to pinpoint high-risk factors that could potentially disrupt project completions by scrutinising periods or contractors under intense load.
-
To make this complex information more digestible, I decided to use a ribbon chart to display demand over time, effectively spotlighting high-demand periods. Alongside this, I used a scatter chart concentrating on contractors, and the distribution of project statuses, once items in these charts are selected it updates all charts, this allows the user the flexibility to examine the data from various perspectives, not possible when in a flat tabular format.
Build
-
For quick high-level insights, I incorporated a sidebar on the left.
-
I created a ribbon chart that revealed the monthly distribution and requirement changes for project disciplines to help understand the demand by project start month.
-
I designed a scatter chart that clearly highlighted the relationship between the number of projects and the total value by contractor. Additionally, I added a tooltip feature to give a detailed view of project status distribution, assigned projects, budget allocation, and the split amongst project directors
-
A bar chart to represent the distribution of project risk profiles.
-
line charts to depict the yearly trend of the top three disciplines based on volume.
-
To relay key figures related to these top three disciplines, I used info cards.
-
A clustered column chart to display the distribution of project statuses, supplemented by a tooltip summary of all projects within the chosen project status.
-
Finally, I incorporated two matrix tables to represent high-level data; one showing the number of projects by discipline along with their PO values, and the other revealing the number of projects by project director, including their assigned PO value (or budget responsibility).
Review (what additional features could be added?)
Error checking to flag to data owners
-
An error checking sheet highlighting the errors in the raw data, for example where related variables have conflicting status: Project status = Complete, but project completion percent = <90%)
-
This has been really useful especially in obtaining buy in and more proactive action from the data owners, and something I personally include in all dashboards, as it helps give me confidence in the data we are presenting, especially if a data source needs to be updated manually if there are no suitable/available systematically driven methods.
​
Financial performance
-
Spend to budget/plan, risk part to whole, spend forecast, spend flags
-
Invoicing status and age profile of invoicing to help identify bottlenecks
-
Purchase order variations and tracking, time taken to process, volume outstanding
-
Compliance with financial process, accuracy in finance reporting
Project delivery
-
Project delivery vs schedule
-
Deeper detail into risk, HSE, Financial, external factors
-
Auditing
-
Escalated issues
-
High profile projects in sensitive locations
-
Regulatory/Statutory compliance
Subcontractors
-
Deep dive into contractor performance against contractual KPI measures
-
Subcontractor performance benchmarking against competitors, cost, risk, SLA, HSE etc, performance and compliance
-
HSE dashboard, near misses, incident reports, Risk assessments/method statement audits