Burndown Chart in Power BI - Visualizing Project Progress
CREATE EASY TO INTERPRET INSIGHTS TO DISCUSS PROJECT PROGRESS
Managing a BI or IT project and having a clear overview of the status of hours spent and available can be challenging. We often only look at the beginning budget and the total hours spent, with a negative amount meaning an exceeded budget. It often seems a mystery “when” and “why” a project was delayed; afterward, it’s hard to trace back what happened. The “why” of course, should be discussed with the team, but the “when” can be visualized – using a burndown chart in Power BI (or any other BI tool).
A burndown chart is a data visualization that can benefit project managers, BI/IT teams, and stakeholders. It shows hours spent, planned, and remaining hours (the line in the visual), giving you immediate insight into whether the project is on track, delayed, or at risk of exceeding the budget.
Burndown chart benefits
- Visually represent hours spent, planned, and remaining.
- Identify potential issues or delays early on.
- Sharing the burndown chart with stakeholders and the team provides a clear understanding of the current status. It sets realistic expectations and creates a sense of trust and collaboration.
- Starting point to discuss what has been accomplished so far, what is planned, and if that's realistic given the remaining time.
- Opportunity for learning and evaluation, allowing project managers to pinpoint overspending hours.
Creating a burndown chart in Power BI
To create a burndown chart in Power BI, you do not need an additional visual from the AppStore. Creating a measure in DAX and using a line and column chart can do the trick! You need:
- Power BI visual: Line and column chart
- Specification: for example on project level (could also be project activity, employee, depending on what the hours budget is based on.)
- Date Information: For example, a week, but this could also be a date, a month or a year, depending on the type of information you want to visualize.
- Initial hours budget, hours spend and hours planned (if available)
Step by step guide - creating a burndown chart in Power BI
A burndown chart in Power BI can be created in 4 steps when the information required (budget hours, hours spent, hours planned (if available)) and date are available.
Step 1 - Total Budget Hours
First, you want to know the total budget of hours available for a specific project. To get this information, you summarize the budget information you have available for a project.
In the example DAX, you see that also an ALL function is used. It ensures that the calculation is performed on all project IDs, regardless of any filtering or context in the report self.
Hours_Project_Budget=
CALCULATE ( SUM ( Project[Budget Hours] ),
ALL ( Project[Project ID] ))
Example DAX Measure: Total Budget Of Hours
Step 2 - Cumulative Hours Spent
Second, you need a measure that provides the cumulative hours spent. Cumulative means adding up:
| Week | Hours | Cumulative |
| 1 | 5 | 5 |
| 2 | 2 | 7 |
You need this measure by day (or week, month..) to understand how many hours are spent counting up. This way, you can, in the next step, subtract the hours from the budget hours.
In the DAX formula, you also see a filter on a date. This filter ensures that only hours are added up until the specific date (otherwise, all hours would be added up, regardless of the date).
Hours_Spent_Cumulative =
CALCULATE (
SUM ( 'Project Activity'[Hours Spent_and_Planned] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
Example DAX Measure: Cumulatief Hours Spent
Step 3 - Combining Step 1 & 2 To Create The Burndown Line
The final measure is a combination of steps 1 and 2. In the DAX, you see that I used variables for measures 1 and 2 and then used a return statement to subtract the results of steps 1 and 2.
This also means that you do not necessarily need to create separate measures in steps 1 and 2, but for learning purposes (and testing), creating these measures in the first place can be helpful.
This measure will subtract the cumulative hours spent from the budget depending on the pint of time (date). This is also the measure you will need to use as line in the line & column chart.
Burndown line =
var Hours_Project_Budget=
CALCULATE ( SUM ( Project[Budget Hours] ),
ALL ( Project[Project ID] ))
var Hours_Spent_Cumulative =
CALCULATE (
SUM ( 'Project Activity'[Hours Spent_and_Planned] ),
'Date'[Date] <= MAX ( 'Date'[Date] ))
return Hours_Project_Budget - Hours_Spent_Cumulative
Voorbeeld: Combineren stap 1 en 2
Step 4 - Creating The Burndown Chart In Power BI
To create the burndown chart, follow these steps:
- Add the line and column chart: I used a line and stacked column chart in this example because I differentiate visually on hours spent and planned.
- X-axis: Put the date information (example uses “Week”)
- Y-axis: Hours spent and planned (or a combination, or only hours spent, depending on which information you have available)
- Line Y-axis: The burndown line measurement created by following the previous steps
How and when can you use a burndown chart?
- Communication with stakeholders: Integrate the burndown chart into a dashboard/report and use it during project update meetings.
- Communication with the project team: A visual representation of the hours spent and planned can make it easier to identify potential risks of exceeding the planned hours. Providing this information in a visual way can make it easier to start discussions with the team during a standup/weekly meeting.
- Overview for project managers: working on more than one project a burndown chart can be an easy way to quickly see project progress for different projects (you could use multiple charts in an overview dashboard).
The burndown chart in this article is based on the hours budget for a whole project. If the information is available, you could also create insights into project activities or, for example, employee performance. This, of course, all depend on the data available to you.