Burndown Chart in Power BI - Visualizing Project Progress
Interpret project progress in seconds with a Power BI Burndown chart
Managing a BI or IT project often involves tracking hours spent versus the remaining budget. Typically, we look at the initial budget and total hours spent, with a negative balance indicating an exceeded budget. However, understanding when delays happen in a project can be difficult, making it harder to act on time.
This is where creating a Burndown Chart in Power BI becomes very valuable. It allows project managers, team members, and stakeholders to visually track project status so that everyone can quickly see whether a project is on track, delayed, or at risk of exceeding its budget.Β
Benefits of a Burndown chart
- Visualize hours spent, planned, and remaining.
- Identify potential delays early on.
- Sharing the burndown chart with stakeholders and the team clearly explains the current status. It sets realistic expectations and creates a sense of trust and collaboration.
- Discuss what has been accomplished so far, outline what is planned, and evaluate whether itβs realistic given the remaining time.
- Gain valuable insights to improve future projects.
Creating a burndown chart in Power BI
You can create a Burndown chart in Power BI using a core visual: the stacked line and column chart. The required measures are created using DAX. Hereβs what you need:
π Power BI Visual: Stacked line and column chart
π Specification: This could be at the project level or broken down further into project activities or employees, depending on the data you have.
ποΈ Date Information: Weeks, days, months β whatever works best for your project.
π Data Points: Budget hours, hours spent, and planned hours (if available).
Step by step guide - creating a burndown chart in Power BI
When you have the necessary data, such as budget hours, hours spent, hours planned (optional), and dates, you can create a burndown chart in Power BI in four steps.
Step 1 - Total Budget Hours
First, you need to determine the total budget hours available for a project.
The ALL function removes any filters applied to the Project[Project ID] column in the DAX example. This ensures that all projects’ total budget hours are calculated, even if filters or slicers are applied elsewhere in the report.
Project Budget Hours =
CALCULATE (SUM( Project[Budget Hours]),
ALL( Project[Project ID] ))
DAX measure to calculate the budget of each Project.
β β β Step 2 - Cumulative Hours Spent
Second, you need a measure to calculate the cumulative hours spent. Cumulative means adding up the hours.
| Week | Hours | Cumulative |
|---|---|---|
| 1 | 5 | 5 |
| 2 | 2 | 7 |
In the next step, we will use this number to compare it against the budgeted hours.
In the DAX, you see a date filter. This ensures that only hours spent up to the selected date are included, so you wonβt accidentally sum up hours from future dates.
Total Hours Billed and Planned =
CALCULATE (
SUM ('Project Activity'[Hours Billed]) + SUM ('Project Activity'[Planned Hours]),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
DAX measure to calculate the hours spent. I calculate it for the hours billed and planned. You could also choose to calculate the hours spent.
Step 3 - Combining Step 1 & 2 To Create The Burndown Line
Now, you can combine the total budget hours and cumulative hours spent into a single measure. This measure subtracts the cumulative hours spent from the total budgeted hours.
You can then use this measure as the line in your stacked line and column chart to visualize the project’s progress against the budget.
3 Burndown Line = 'Project Activity'[Project Budget Hours] - [Total Hours Billed and Planned]
The measure subtracts the cumulative hours spent from the total budgeted hours.
Step 4 - Creating The Burndown Chart In Power BI
Add the Stacked Line and Column Chart: to differentiate between hours spent and planned.
X-axis: Add the date information (e.g., “Week”).
Y-axis: Place hours spent and planned.
Line Y-axis: Add the Burndown Line measure (created in step 3) to visualize the remaining hours.
How and when can you use a Burndown Chart?
π¬ Communication with Stakeholders
Integrate the burndown chart into your dashboard or report and use it during project update meetings to keep stakeholders informed about the project’s progress.
π¬ Communication with the Project Team
A visual representation of hours spent and planned can help identify potential risks of exceeding the planned hours. It can also facilitate discussions during standups or weekly team meetings.
π Overview for Project Managers
If you’re managing multiple projects, a burndown chart offers an easy way to track the progress of each one. You could even create a dashboard with multiple charts for an at-a-glance overview.
The burndown chart in this article is based on the total hours budget for a project. If more detailed data is available, you can expand the insights to include project activities or even employee performance.