In this article, I will run through all the steps required to produce an elegant Engineering Progress Report.
The intent is not to delve into the manner in which the progress or schedule are updated. I have assumed you have a schedule and progress status for each key area. It is quite amazing how easy is to generate this dashboard, and also the extensions available to use this not just for engineering, but for fabrication, material deliveries, major milestones, contractor key activities, etc.
I will outline the format for our 2 key datasets and then follow with the creation of 2 dashboards: An Overall Status Gauge, and the full detail EPR Dashboard
P6 Schedule Data
Below is our data set we want to use. This data set has been specifically tailored to our resulting visual. Thus, instead of linking directly to an XER, importing into a data model, and performing perhaps too much data work, a nice trick is to instead define specific VIEWS inside P6, so that you can easily copy-paste directly into Excel, then import directly into your dashboard. Thus, the below can be quickly generated each schedule update cycle.
A very nice aspect of this data set is the field “TYPE”. It is good practice to tag activities of a specific type (this ties into my belief about using a framework approach to approach controls). Thus, in theory, you can export the entire schedule, and drive many different dashboards by just filtering on different TYPE fields. In this example I have used
- M090 = 90% Model Review
- M100 = 100% AFC
Although, consider tagging every concrete pour Activity in your schedule with C010. You can then use that code to drive a similar dashboard for concrete pours: Or you use F100 for Module Fabrication, where we tag the completion activity for each module for use in dashboard. Ultimately you create a catalog of TYPE codes and can go dashboard crazy with how easy this turns out to me.
This data does not have all the fields we will need in our dashboard. Specifically we will want to create a several measures that will allow for a few metrics. We will need to know if an activity is “FINISHED”, “NOT FINISHED”, and “Critically LATE”. Because these fields are dependent on your target audience, its best to leave the generation of these to code (because everyone can code right!). If you wanted to display metrics on “Started”, then your source data would need to include the start date and perhaps the activity status field from P6. Again, its important to understand the relationship between your visual and your data. In this example, I am treating these activities as effectively milestones in which case the concept of “started” doesn’t apply. key conceptual discussions such as this are vital.
The progress data in this example is only overall progress. The intents is to just show an overview for the entire project and quick metrics for model reviews. Ultimately, you would want a “WBS Specific” dashboard that would display more information over the entire lifecycle of that WBS. In that view, you could present the engineering curve and perhaps EVMS metrics.
Strategy – Do not do everything in one place- keep focus
Too often, I see users pushing design features into dashboards, for what appears to just be whimsical value. Dashboards are not meant to answer 100 questions. Its easier to have 100 dashboards each displaying a key metric, as opposed to 1 dashboard displaying 100 metrics. Keep your approach CLEAN and FOCUSED.
Ideally, our progress data will include fields such as Area, WBS. In this example I have pulled data with just 1 data date and only 1 dataseries (Engineering_Overall). Your backend progress data will likely have data from multiple cut off dates and for multiple series.
Our progress data will look like this. The full data set will also contain a series for “Construction_Overall” too. This will be used on our summary page to outline the power in using this approach to progress data.
Linking our Data into PowerBI
In this example, both data files are simply Excel based files with the data converted to table. This allows for the easiest importing (and also allows for quick refresh of data). Housing the data in the excel files can also facilitate a movement to a more digital way of thinking (more on that in another article)
VISUALIZATION 1 – SUMMARY GAUGE
I am a firm believer in Overall Project Flash reports. So, when we think about dashboards we should have a starting point our overall project status. Thus, the elements presented here are only a key subset of metrics and visuals I would expect on a Project Status Report dashboard.
In this example, looking at engineering progress, we want to see what Percent % Complete we are and how that compares against our Planned % Complete.
A Gauge is a good way to provide a quick visual (Bullet charts are other, and really, the skies the limit)
To generate this we need to create 2 measures: Actual % and Planned %. This is where you really need to understand how dashboards work and how databases work. If you feed a computer a data source, it is no innate way of know something as simple as “What is the current %”. Therefore, we need to write some code.
Because of the format of our progress data, we can search for the maximum data date, then find the value of our actual % field on that date. We can follow an identical approach for the Planned %. Depending on your data, you would need to custom build these measures.
Code to generate our measure for Current %
M_Progress_Actual = CALCULATE (SUM ( data1[Actual] ),FILTER (data1,data1[DataDate] = MAX ( data1[DataDate] ) && data1[Date]=MAX(data1[DataDate])))
Code to generate our measure for Planned % (similarly we could also pull in our Plan late)
M_Progress_Plan = CALCULATE (SUM ( data1[BL_Early] ),FILTER (data1,data1[DataDate] = MAX ( data1[DataDate] ) && data1[Date]=MAX(data1[DataDate])))
The required fields for the gauge are obviously these 2 measures.
- Value = M_Progress_Actual
- Target Value = M_Progress_Plan
We will also need to provide a filter where Series=”Engineering_Overall” (note that this gauge can now be easily reproduced to showcase planned vs actual for all Series inside our data source. Obviously in the image above you can see I created 2 gauges each with a filter for the specific data series. Ultimately if your back end data has multiple data series for progress sliced and diced different ways, all you have to do is adjust your filter and you can display an endless series of graphs. Of, you can fancy with smart slicers too.
VISUALIZATION 2 – Engineering Progress Report
This is perhaps the most easy to read, interactive and intuitive view into engineering I have ever seen. We can immediately filter into what areas are complete, what areas are critical, scroll to see upcoming deliverables and see an overall graph.
It might seem we have a lot going on here, but again, this is all driven off 2 quite simple data sources, and for this page, mostly everything here is from 1 schedule driven table.
The Data Table
The Table is just pulling from our Schedule data (although I have inserted a page level filter to only include activities with the TYPE = M100 and M090). Our fields are as
In the above image, you can see I have had to insert a few measures. I don’t want to go into them all. I’ve inserted some conditional formatting into the Actual/Forecast date column. To achieve this, I created a measure Activity_Status_Num
Activity_Status_Num = IF(ISBLANK(Schedule[Float]),1,IF(Schedule[Float]<1,2, 0))
Then, with these values I can select a formatting specific just for that column in the table. This is very nice feature of the tables in PowerBI that can add nice level of polish.
The Donut Charts
A nice feature of the donut chart is the count metric in the middle. It is generated from a nice little bit of code as seen below. We have 2 Donut Charts. One for our 90% activity and another for the 100%. Thus, all we need to do is place a visual level filter on each.
IsFinished = IF(ISBLANK(Schedule[Float]),1,0)DonutCounts = SUM(Schedule[IsFinished]) &”/”& COUNT(Schedule[ActivityDesc])
The real power of the Donut Chart is to allow for very quick sorting – after all we want to see the critical late activities right! Just click the red 90% or 100% section.
We have a progress graph too. This is effectively a dumb page level graph. It is not linked to a specific progress series for each WBS. So it will not auto update, and our data model does not link these tables. Although, the graph should add context to the overall page. Deviations from the plan curves, should be viewed by a growing number of critically late packages.
Care needs to be made whenever we look at schedule dates and progress graphs. We do not typically create progress graphs at an activity level (although, you can certainty consider it – I would offer caution against going down that route).
This example has show the power simple data sets can have to improve visibility into our projects. This only showcased a few engineering based activities. However, if you read between the lines, you will understand there is nothing “engineering specific” about what I have done. This approach is completely universal. Given this example also included a progress data set for Construction, obviously, the easiest extension will be to link in a few construction activities in the same way.
8 thoughts on “Engineering Progress Report – PowerBI – by Darrin Kinney”
Hi. Thanks for sharing your knowledge.
I have one question regarding the planned and forecast figures. As I see above for the planned figures you used Baseline Remaining Early Units (BL_Early), right? And for forecast figures? Primavera P6 only shows Budgeted, Early and Late Units in the timeline of the resources assignment.
I would recommend reading a more recent post where I make it a bit more clear how to deal with Baseline, Actuals to date and forecast to go
Thanks for that tip. You would not imagine all the times that I review the content in this blog. It is like a huge library. 🙂 Sometimes it is not easy to pay attention to all the details….
Ok, get to the point I found what I want to know in the Part 2: The Excel Feeder Sheets as follows:
Planned (Plan) = Baseline, Resources Assignment @4:26
Forecast (Fcst) = Latest schedule update, Resources Assignment, Remaining Early Units @ 04:00
An additional question. At the data date, actuals will not match with the (actuals + remaining early units) causing a “jump”in the S-Curve between the actuals curve and the forecast curve. How did you solve this issue?
Hi. The question could be misleading…
This is a particular issue that only happens because my S-Curve axis is per month and the data data does not match with the last day of the month. This originates that “jump” between the Actuals line (at the data date, for example 26.07) and the Forecast line (from the data date until the end of month, for example 27.07-31.07).
Any idea to overcome this?
I make sure my setup is fixed with week endings and reporting periods. So would not (in your example) ever report data on 31july. The only date with data would be the 26July. On that date, no forecast data would exist, only actuals on that date and all prior dates (and you have to check no actuals on future dates). Additionally, I know my forecast to go by summing all the forecast series. So, definition of % is the (Act/[act+fcst]). That figure is the same for the act % on that date AND the fcst % on that date. Again, if you closely follow part 4 where I talk about the measures (and trust me the measures are the hardest part), you will see how I tackle this to ensure my forecast series starts on teh same value as my actuals. Totally agree, these simple concepts in excel: are brain teasers in PowerBI.
Great post! How can I have the database to try to creacte this Dashboard on my own?
Is there civil construction database generator site?