In my years reporting on project controls, I’ve often been asked to visualise financial benefits over time. Tracking realised vs expected benefits is crucial for decision-making, but finding the right way to present this data can be tricky.
At first, we tried using a waterfall chart, which seemed like a good idea. But in practice, it wasn’t as clear as we had hoped. While it broke down financial movements well, it didn’t provide that cumulative, at-a-glance view that stakeholders needed to quickly understand progress and gaps. Additionally, this type of visual didn’t offer much flexibility in terms of formatting, limiting our ability to effectively communicate the data.
Then, I came across Valerie Junk’s tutorial on Burndown Chart in Power BI, and I had a Eureka moment. If we can track effort burn-down over time, why not apply the same logic to cumulative financial benefits? That’s when I built this Financial Benefit Burn-Up Chart in Power BI, and, I want to share how and why it works. This will be done in three parts:
- Part 1: This article will explain why this visual is important and break down the data model.
- Part 2: I will walk through the calculations in DAX needed..
- Part 3: A video on my YouTube channel @AccessibleBI, where I will show how to build the chart step-by-step.
To give you a glimpse of what’s to come, take a look at the significant difference between creating the visual using a waterfall chart and the second method I’ll be demonstrating here.
Why This Visual is a Game-Changer for Project Controls
One of the biggest challenges in project controls is ensuring stakeholders can easily compare what was planned vs what was achieved. This chart solves that by layering 4 key components:
- Blue bars: Benefits already realised.
- Grey bars: Benefits expected to be realised in the future.
- Dashed grey line: Baseline showing expected progress.
- Orange Error bars: Cumulative Benefit Loss.
In the chart, the Outturn series is represented by the bars that have been conditionally formatted:
- The blue bars indicate the benefits that have already been realised.
- The grey bars indicate the benefits that are expected to be realised in the future.
Since the totals are cumulative, the realised benefits (blue) and expected benefits (grey) build upon each other month by month. If the realised benefits are lower than what was initially expected (Baseline), it will impact the overall calculation of the outturn.
This way, the chart visually communicates the gap between what was expected and what has been achieved, helping to track and manage performance over time. The actual values for the variance can be detailed under the tooltips for further insights.
With this setup, you can instantly see how well the project is delivering against expectations. If the grey bars start stacking up, you know there’s an issue, expected benefits aren’t materialising. Instead of sifting through tables of data, project teams and executives can spot financial gaps early and take action.
Building the Financial Benefit Burnup Chart
1. Data Model
First, let’s ensure our data model is properly structured. We’ll need a Facts Table that includes Project/Programme Benefits (including the Cost Expected, Cost Realised, and End Date), along with the appropriate Dimension Tables. We must have a Dimension Dates table with unique and consecutive dates. If you missed my previous post on the importance of a Dimension Dates table, you can catch up on it here.
Your data model might require additional Dimension tables depending on your specific needs, but for this example, we’ll focus solely on the Dimension Dates table.
In our data model, the Facts_Programme Benefits table has a relationship with the Dim_Date table, established through the ‘Benefit End Date’. This relationship is significant because the Benefit End Date represents the date when each benefit is expected to be fully realised.
We’ve now walked through the essential structure of our data model, ensuring it’s well-organised and ready for our visualisation tasks. This foundational setup is critical for the success of our Financial Benefit Burn-Up Chart.
Stay tuned for the next article, where we’ll dive into the specific DAX calculations required to bring this chart to life.
Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!
