Category: Dashboard

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.

Three years to finish a Dashboard

in 2017, at my previous job, we were using PowerBI Desktop as our reporting solution, but there was a big limitation, we couldn’t use the service, so sharing the reports was either in Excel or pdf.

I remember I did try different solutions (Rstudio, Qlik, SSRS), they were great Products, but you need some kind of server to share the reports. At that time all I wanted is a simple web app where people can click on a slicer and get a fancy charts.

At that time Google made their reporting solution free, I was really excited about Data Studio, a free product, extremely easy to share but unfortunately a bit slow and lacked some basic functionality, I still managed to build something but it was not really good

It is all history now, moved to another job, we have PowerBI service ( and Tableau), but still for some reason, I felt like a missed opportunity, what if Data Studio became a good enough to be used as a free report tool.

If I remember correctly 2017 and 2018, there was no major progress but then they released custom viz, which basically means you can port any javacript library relatively easily , I managed to build a custom viz see example here

and in sept 2019, BI Engine showed up !!

It was really a big Deal, BI Engine is an analytics in-memory Database , and it is fast and they gave away 1 GB for free, it means you can connect your data from BigQuery and pay nothing ( with a fair limit of course), this made this report possible

In May 2020, they finally released Google Map Integration , although with a limit of 10K points, it was not useful for my use cases ( Solar farm needs a lot of point around 40k to 60K)

That was great and all, but still I couldn’t write complex measures easily ( or maybe did not know how), but something changed in August 2020

At last we have Proper support for parameter, that changed everything, now you can write any complex business logic using SQL in BigQuery and visualize the results using Google Data Studio, and you can do a lot of fancy stuff see those examples

Still there was still a major bug, Pivot table in Data Studio show 0 for null values needless to say, it is extremely annoying although you can build workaround, it was a hack and not sustainable.

That was fixed last week

So yes, it took me three years to finish this report, BI Engine + Parameter + Custom Viz and a bug fix in the Pivot Table to make this report possible

I added a workflow explanation in the report, but basically create a reporting dataset as large flat fact table and show the results in BigQuery with further control by SQL Parameter, if the native visual are not satisfying, you can show pretty much anything using Vega-lite custom viz.

One aspect was impossible to do without Parameter is the dynamic grouping of dates, in the time series, the weekend update dynamically based on the cut off selected.

Please don’t get me wrong, there is still a lot of work to be done, but the foundation of the product is already there, I can see clearly the vision of the product team, hopefully they keep investing but faster this time ( Parameter Action, support for BigQuery geography field, analytics functions, Tiles for Custom Viz ……)

Take away:

– If you need near real time reports

– You want a reporting solution and don’t have a decent budget.

– Used Data Studio in 2017 and dismiss it.

I have a good news for you, BigQuery/Data Studio is a viable option now, and you get 1 TB free for BigQuery and 1 GB compressed in -memory for BI Engine, that’s a lot of free resources, and there is no catch, you can share securely with anyone, again totally free.

Although I am a PowerBI developer and I love it, I think it is very healthy for the industry to have more choices, 2021 will be exciting !!!

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

NASA COSTS

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

Extensions

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

PowerBI Progress & Schedule Dashboard – By Darrin Kinney

I have recently been developing a series of videos that highlight the key features utilized in a progress and schedule dashboard. The videos showcase the capabilities of PowerBI dashboards in the Project Controls space. I have not seen dashboards effectively used in this way and want to share the valuable knowledge.

I have recently been developing a series of videos that highlight the key features utilized in a progress and schedule dashboard. The videos showcase the capabilities of PowerBI dashboards in the Project Controls space. I have not seen dashboards effectively used in this way and want to share the valuable knowledge.

This series is not meant to be a step by step guide. There are subtleties about this demo that may cause difficulties in the production environment. I would simply recommend you share this with your development team and discuss the pros and cons of your approach. Oftentimes, a more straight forward approach is more valuable when compared to endless development polishing an inferior product.

Part 1: The Showcase

This video gives an insight into the key capabilities of the dashboard. Having the ability to seamlessly review schedule activities, and how these contribute to the overall progress and forecast, is invaluable.

The ability to quickly dive into your schedule, without having to deal with the confines and limitations of your actual scheduling tool are also key features.

Part 2: The Excel Feeder Sheets

This highlights a simple Excel feeder sheet. Too often the time phased data that our schedules produce are not easily accessible in a digital format. I have built an excel file around a typical structure that project controls deals with. This structure will lend itself nicely to the steps that follow in converting the into a database format.

Part 3: PowerBI PowerQuery

Here we import the data from the Excel feeder sheets into the PowerBI platform. The use of PowerQuery is so embedded with the way PowerBI works. The steps you need to follow here are the similar to the steps you would need to follow in inserting the progress and schedule data into any formal data structure. The way we think about data is sometimes not compatible with the format that databases need. This is specifically around the need to “unpivot” time phased data.

Part 4:  PowerBI Measures and Dax

With all the data now structured and available to PowerBI, we need to now dive into the use of DAX to create Measures. A perfect example in the use of measures is in the generation of progress curves.

What might seem line a straight forward approach to drawing simple progress curves, is in fact (within the realms of PowerBI) not that simple. However, if you follow a logic approach and know what calculations are needed, the world is your oyster.

Part 5: Integration of JIRA and Agile Methods

In the (as of now) final installment of this series, I showcase a way in which we can integrate our PowerBI dashboard with a JIRA project. This approach is completely different from what you might expect. I don’t want to put a PowerBI dashboard ontop of my JIRA task list. I want to put a JIRA task list on top of my schedule.

The purpose of the dashboard is to extract the SCHEDULE data from the scheduling tool. When variations to prior forecasts occur, or where further detail is needed, we are often constrained because pictures, running commentary and discussion about each activity is not something that resides in our schedule. However, we can use JIRA to easily capture those elements and use our PowerBI dashboard and a linking tool to integrate everything together.

The Future: ???

There are still a lot of features and extensions that I have yet to formally discuss. The next steps are likely going to be a showcase of a SQL Server backend for this data. There is a lot of information that is missed in the way this dashboard imports data (specifically past budgets). Therefore visibility into changes is restricted.

Another interesting feature is the use of saw tooth graphs when budget changes occur. I have a clear vision for how this is possible, and in a professional capacity have implemented it. To achieve this, while you won’t achieve a discontinuous graph, it will calculate your % based on a variable budget. For this, you need to setup another tab on the excel feeder which instead of tracking the earned per week per activity, it will instead track the BUDGET for each activity at each cutoff. With this data element now in your data, you just change the denominator calculation.

In general, the way in which dashboards and data are embedded into our work processes, is a field ripe for growth. It is also an endeavor that can greatly increase the visibility into project controls data and can also bring teams together using integrated tools like JIRA. As such, the future is bright and where we should always have half an eye looking.