PowerBI Custom Chart Ranges

My go to progress dashboard calculates a lot of progress % metrics and graphs. However for larger projects, its often difficult to zoom into the graph. This is exactly why slicers exist

Slicers are not my specialty, actually, PowerBI is not my specialty. So it was with a little frustration in trying to add a slicer to a page to find the slicer altering all my data.

Set The Stage

My go to progress dashboard calculates a lot of progress % metrics and graphs. However for larger projects, its often difficult to zoom into the graph. This is exactly why slicers exist. I am sure there are other nice graph tools that allow for custom date ranges, but again, this is so not my specialty.

What didn’t work

I simply added a slicer on my “weekending” field. However, in doing so, all my measures are now calculating based on the filtered date range. This is likely an issue with my measures, but alas, I wanted something to just adjust the graph axis and not effect anything else

Below we can see that my measure are calculating a progress set from 0-100%. Thus when the date ranges were adjusted, the entire dashboard is now just wrong. My budgets and %’s are also not correct on the cards (which are also based on all the slicers).

The Solution – Create a Duplicate DIM_Date Table

The problem was caused because the slicer was based on the live master dimension table that was linked to my data. Just like I want my graph to adjust based on the adjusted the WBS dimension tables, if I insert a slicer linked in anyway to my FACT table, I am in a world of hurt

Thus, just create a duplicate DIM_Date table. Here I created a new table: DIM_Date_GraphRangeSlicer

I insert a formula into the chart X-Axis range to select the min and max dates from this new GraphRange table. I then setup a slicer that filters the range for this new table, not the master DIM_Date.

With these new ranges, linked to the dummy date range, I can now much better refine just the X-Axis display of the graph without impacting any of the measures used to calculate the % progress.

The Result

Putting it all together, we can now customize the X-Axis range without altering the measures or cards that are calculating key metrics off the full (or filtered based on the WBS slicers) data.

PowerBI – Resource Profiles from P6

One of the biggest critiques/limitations in using P6 data is the lack of time phased resource assignment data. The only effective way to pull time phased resource assignment data into PowerBI (or even excel ) is to copy-paste from P6 into Excel. This is what I have recommended in the past and still what I would recommend for anyone moving forward. However, that does not mean that PowerBI can’t produce time phased data using a start date, end date, and profile. What follows is a simply guide on how I have tackled the problem (and the limitations I have run into)

One of the biggest critiques/limitations in using P6 data is the lack of time phased resource assignment data. The only effective way to pull time phased resource assignment data into PowerBI (or even excel ) is to copy-paste from P6 into Excel. This is what I have recommended in the past and still what I would recommend for anyone moving forward. However, that does not mean that PowerBI can’t produce time phased data using a start date, end date, and profile. What follows is a simply guide on how I have tackled the problem (and the limitations I have run into)

Note: In all likelihood this problem has already been solved my many people in many different ways. I do not want to suggest this is “the way” to solve this. More so, I want to simply raise awareness of at least my approach and welcome comments and feedback on how to really solve the problem

Part 1 The Problem

P6 data does not contain time phased distributions. Instead, the backend (and inside XER) data only contains information about the activity, the resource, and the profile applied. So, our problem is to try to extract these data elements and generate a time phased distribution of the resource according to the profile and activity start and end dates.

Typical Activity Level Data
Here we have our Resource Details (including our “curv_id)
Resource Profile Data

I’ve taken a few liberties with the data above to try to focus on the:

Key Problem: how to allocate the resources assigned to an activity according to a resource profile?

I’ve seen this done in excel a lot although, i’ve never been comfortable with the excel solutions. Typically we count the weeks and distribute the hours equally to all the weeks. I know more complex files exist that allow for spreads using profiles. So to add to the problem isn’t nessessarily to simply spread per the profile, but to perform the operation inside PowerBI (or perhaps more to the point, inside Power Query).

At this point, I’d again love to call upon anyone who has a nice solution to include links about how you tackled this as what follows is just my initial stab at this.

Part 2: Getting the data into PowerBI

Firstly, although the data above is from a P6 XER file, I wanted to make this a bit more general. Therefore, I have created a toy model approach. Thus, here is what my source data looks like

Resource Profile Data:

Excel Profiles

We can run this through PowerQuery and convert it to a usable table. Note in the above I have created a PeriodCum field. This will be used to calculate an end date for each of the 10 periods required.


Activity and Resource Data:

In this example, I am combining the Activity and Resource data into just 1 table. Obviously if you were doing this formally, you would need to build a scheme to link the Resource Assignment data into the Activity level data

Excel Data

And running the above through Power Query we end up with something as seen below. Note I have added a calculation for the duration (in cal day) and have converted the date formats to numbers. This make the subsequent steps a little easier

Note: a critical hard step (for me at least) came duration this stage. Because we are breaking the duration into 10 periods and will ultimately be allocating a qnty per day to each each, if we have a fraction of a day (example a duration of 15 days), my method bombed. This caused an overlap of qnty allocation on the day of the overlap. As such, I have rounded the duration to the closest 10s.


Part 3: Time Phasing (where the magic happens)

The first step of generating the time phasing is to now split the activities into the 10 periods per the resource allocation. We do this by first merging tbl_activies with tbl_profiles using the ProfileDesc field. After expanding the result, we will end up with 10 records for each activity (corresponding to each of the 10 periods).

We will now want to calculate a start and finish date unique for each of the 10 periods. In the profile table is a PeriodCum field that we can use to multiply by the duration and then add that to the start date to get a finish for each period. The result table will now look something like this. Note, at this step, its good to now use the profile allocation for each period and multiple that by the hours_total field. This will give us a hours per each period. The last step will be the divide that by the period duration to calculate what will now be an hours per day for that period.

Lastly, we now want to perform 1 additional expansion to get the DAYS for each period. Here is the code I have used. This is a nice little bit of code that can generate a sequential list from a start to an end (we are using days, but its works for any beginning to end sequence)

= Table.AddColumn(#”Changed Type2″, “Custom”, each { Number.From([PeriodStart])..Number.From([PeriodFinish]) })

resulting resource allocation table with profiles applied

In our resulting table (see above), we can clearly see that the hours per day adjusts for each period based on the profile.

Part 4: Putting It All Together

We have our activity data, we have our time phased resource data, the last step is to generate a DIM_Date table that can be used to bin the days to either weeks or months (or quarters or years or any custom grouping defined inside the DIM_Date table)

I don’t want to get to into the DIM_Date table, effectively all we need is each day assigned to a Month-Yr for the purpose of generating a nice little graph below, which is ultimately what we are after.


Again, I do not recommend using this approach. Instead I am more interested in how others have decided to approach this. Personally, as I noted at the beginning, my recommendation is to use a copy-paste from the P6 Resource Assignment tab. Although, this time phasing approach can be used for other (non P6) applications. But alas, I believe there are much smarter ways to achieve the spread using the DIM_Date table and perhaps custom functions. In my research for my method, I ideally wanted a “working day” spread as opposed to the calendar day spread. There are some awesome custom functions that can provide an integer for the number of working days between 2 dates. However, even when taking that approach, I ran into additional complications in getting everything to work.

So, really keen for feedback!


NASA Apollo Cost Tracker

Quick how to guide on building my NASA cost tracker.

To follow up a recent video showcasing the NASA Apollo Costs, I wanted to illustrate how easy it is to use PowerBI to generate quick program of works dashboard. If you have several projects following a pipeline of work, some features here might spur some discussions or thoughts on what is possible.

The Data

I have sourced data from a google drive folder


However, like most data you find, the format is not suited to analytics. So a little manipulation was in order. Firstly, I had to create a WBS structure.  Typically, information we find is buried under headers, however for databases, we need to turn group headings into a column data field.

We can see I have inserted a 3 layer WBS structure, plus a company name field. This will allow me the flexibility to add subsequent data to this file from perhaps multiple companies, not just NASA. Again, when you build flexible data structures, the way you can use the structure is much more powerful

I know that I also want more contextual information displayed on the dashboard beyond simple data. Specifically, I want a description blurb to be viewable on a tool tip, along with a picture. Additionally, I want to display the leading contractor as well. Therefore, I added a few columns to the excel file. When you import the data into PowerBI, the URL needs to be set as a special format of “Image URL”. Took for some time to find that setting: its under “data category” on the column tools tab.

At some point, I will hopefully build out this dataset to include subsequent NASA budgets, and also publish this data through an API that everyone can access. However, there are limitation to what I can do and what I want to do typically far outstrips my abilities.

The Dashboard

Importing the data is quite straight forward, we do need our usual “unpivot” trick to convert the year information (which is contained inside columns) into row based data. However once that is done, lets look the various parts of the dashboard.

Before I jump into the various aspects of the dashboard, what really gives a dashboard a little polish is the use of a background image. Here is my go to ground image. Just a little playing around with Paint can produce something very valuable to your end product.

The dashboard utilizes 3 slicers. Each has a slightly different formatting. I definitely recommend playing around with the formatting of your slicers

The TREEMAP is where I have put a little extra bit of attention

What pops out here is the tooltip. I have created a separate page just for this tooltip. I am by no means an expert in designing tooltip, but know the power of inserting extra dimensions of data that again allows your dashboard to pop. This specific tooltip includes the blurb, an image URL and the main contractors. This information would be too dense for the overall dashboard and perhaps not dense enough for its own dash, therefore a tooltip is a perfect medium between.

The final element of the dashboard is the line graph and histogram. I still find creating line graphs difficult and in this case I had to add a measure to my data. I think there is a much easier way to achieve rolling sum data, but in my case, the below measure works easy enough for me.

CTD_line = CALCULATE(SUM(NASA_Budgets[Value]),filter(ALLSELECTED(NASA_Budgets),NASA_Budgets[Year]<=MAX(NASA_Budgets[Year])))

And with that, we have our completed dashboard


There is a lot I can do with this framework now. We have a cost file that is quite generic and a dashboard that is also generic. We can in theory use this to outline any type of project pipeline. Although this dashboard is looking in the past, we can also have a rolling wave where we can see past spend on specific projects and what our future pipeline of work looks like. I love seeing project pipelines and following my NASA theme for the moment, here is a great view of what the NASA project pipeline looked like in 1973

Integrated EPCM Management – Engineering Progress

How is an engineering deliverable list a bit of a misnomer? Find out of a web page with editable fields can streamline the tracking of progress on list items.

I am a firm believer in the use of a standard project website portal from which the project team at large can quickly access key data and metrics about the project. This is not meant to be confused with a project dashboard, or PowerBI visualization. This is simple html file linked to a database with key flat-file information. Using smart JavaScript code, it is also possible to EDIT some of the key information.

With everything, before we even begin, we want to focus on what core digital strategies we are trying to tackle. Again, the project website is just a tactical approach, underlying it are the more relevant strategic goals we want to operate under.

Digital Strategy – Make Information easy to access

Digital Strategy – Agile Construction Management

Digital Strategy – Allow people to EDIT key data

The features discussed in this blog can be seen showcased in the following video. This is not a pipe dream. This is a functional application.

The video can be viewed in a separate window at https://www.youtube.com/watch?v=0XNA9xJS2yY

The Data

The key data sources involved with engineering progress will be:

  • Engineering Deliverable List
  • Manhours per WBS

Your engineering deliverable list is a bit of a misnomer. A lot of engineering and design tasks are not specifically related to a “deliverable.” In my view of the world, while you will have specific deliverables and need to track them, your progress list should also include everything else you are doing too – up to a point where perhaps the level of detail is too small.

For analysis, productivity factors for engineering are critical. As such, this post would not be complete without a discussion and visibility into hours and productivity factors.

The Menu

Our menu follows many of the key functions of project controls. However this specific post will dive into the ENGINEERING section only.

If we expand our menu, we see various views into our data: by project, discipline, contracts, schedule IDs and a unique view for controls engineering

Detail Views / EDIT MODE

Perhaps before we look at our summary and drill downs, the main control screen we would use on a day to day basis would be the Deliverables by WBS and Discipline

In the below screen, we can see all out progress items, our budget hours, progress% as well as the mapping to P6 ID (as all progress items should be mapped to into your schedule)

This view into deliverables by itself is not specifically unique. Where the magic happens is when we enter EDIT MODE.

Inside our EDIT MODE, we can directly update the progress %, and update our mapping to schedule ID. These features in my mind are your killer features that distinguish this from any other app. The shear ease in updating items in this way is a breath of fresh air, not to mention the live feedback and visibility throughout your team.


Each combination of WBS and Discipline can be mapped to a JIRA task. Obviously we know our WBS can be considered an EPIC. The power in using a tool such as JIRA is that we can now track more detailed L4/5 tasks using JIRA Subtasks. These are completely flexible to allow the user to manage their own tasks.

Additionally, the ability to embed commentary and status is a brilliant way to distribute and communicate key status and blockers if any.


Summary Pages

Now that we have seen how we will actually interact with a project website, we can now showcase the summary level reporting. Specifics of how you report can often times be better captured using an analytics platform like PowerBI; however, more often then not, simple summaries and metrics are what drive our business.

In the summary by project, we can easily see key metrics per project. Each project would have a link to allow us to drill into the details for each project

In the above, we are now looking at the individual functional delivery areas (disciplines) for our project. Again we can see key budgets, spent hours, progress and productivity measures too. By clicking each discipline, we further dive into our project by now looking into the detailed WBS elements for that Discipline

It is in this view that we can see detailed metrics per WBS/Discipline pair.

Understanding our Spent Hours from time sheets is a critical management function. As such, the SAP actuals here is a link that takes us to a screen where we can view the detailed weekly time sheet data to see who (and when) has book to this element.


In summary we started with some key strategic thinking and built out a tool that ticks a lot of boxes in the EPCM construction world.

More often then not, I am confronted with technology that “shows me” but doesn’t allow me to interact, edit, or collaborate. Everyone wants to solve the “one source of truth” however, information changes and is updated by people. That is the missing link in a lot of our data analytics. Look into the work processes that generate information in the first place. Look at what people need to better capture their raw information into a data format and platform that others can now use.

This approach to put management of engineering items to plain sight, with ease of access is just one approach. There are 100s of ways to approach this, but if you stick to core strategic ideals, you can’t go wrong.

%d bloggers like this: