How to Create a Burndown Chart in Power BI

A burndown chart is one of the most effective tools for tracking project progress, instantly showing your team if they are on track, ahead, or falling behind. While the concept is simple, building one in Power BI that accurately reflects your project's ideal vs. actual pace takes a bit of setup. This article will guide you step-by-step through creating a dynamic and clear burndown chart in Power BI, from setting up your data to interpreting the final visualization.

What is a Burndown Chart?

A burndown chart is a graph that shows the amount of work remaining on a project over time. It's a favorite in agile project management methodologies like Scrum because it provides a quick, shared view of progress within a specific period, such as a two-week sprint.

A standard burndown chart has a few key components:

• The X-Axis (Horizontal): This represents the project timeline, typically measured in days.
• **The Y-Axis (Vertical): This shows the work remaining, measured in story points, hours, or the number of tasks left to complete.
• The Ideal Work Line (or Guideline): This is a straight diagonal line. It illustrates a perfect, consistent rate of progress from the start of the project (with all work remaining) to the end date (with zero work remaining). It's the benchmark you're aiming for.
• The Actual Work Line: This line shows the actual amount of work your team has left on any given day. It will fluctuate above and below the ideal line as your team completes work.

By comparing the actual work line to the ideal work line, anyone on the team can instantly understand the health of the project without needing to dig through spreadsheets or task boards.

Preparing Your Data

Before you can build anything in Power BI, you need well-structured data. Your chart is only as reliable as the data it’s built on. For a good burndown chart, your dataset should contain the following core fields for each day of your project or sprint:

• Date: The specific day. This will form your X-axis.
• Sprint Start Date / End Date: Key dates to define the scope of the chart.
• Total Planned Work: The total scope of the sprint or project at the beginning (e.g., 100 story points, 200 hours). This value should remain constant throughout the sprint.
• Work Completed: The cumulative amount of work finished as of that date.

You can often calculate the field for Work Remaining directly from your source data, but it's just as easy to do this in Power BI. Here’s a sample of what your data table might look like in a spreadsheet, which you'd then import into Power BI:

Example: A 10-day sprint with 80 total story points.

Many project management tools like Jira, Trello, or Azure DevOps can export this data for you. For this guide, we'll assume the data is coming from a simple spreadsheet or CSV file.

Creating Your Burndown Chart in Power BI: A Step-by-Step Guide

Once your data is clean and ready, you can jump into Power BI to start building.

Step 1: Get Your Data

First, import your data into Power BI Desktop.

1. Open Power BI Desktop.
2. From the Home ribbon, click Get data.
3. Select the appropriate source (e.g., Excel workbook or Text/CSV).
4. Navigate to your file, select it, and click Open.
5. In the Navigator window, select the table containing your project data and click Load. Power BI will now have your data ready in the 'Fields' pane on the right.

Step 2: Calculate 'Actual Remaining Work'

You need a calculation that shows how much work is left on any given day. You can do this by creating a new column using DAX (Data Analysis Expressions), Power BI's formula language. It's simpler than it sounds!

1. Go to the Data View (the grid icon on the left).
2. Select your table.
3. From the Table tools ribbon, click New column.
4. In the formula bar that appears, enter the following DAX formula and press Enter. Let's call your table 'SprintData':

Actual Remaining = 'SprintData'[Total Points] - 'SprintData'[Cumulative Completed]

This creates a new column in your table that calculates the remaining work for each day.

Step 3: Calculate the 'Ideal Burndown' Line

This is the most critical part. The ideal line requires a more sophisticated DAX measure, not a column. A measure is a dynamic calculation that changes based on filters and context. We'll break down the formula for you.

1. Return to the Report View (the bar chart icon on the left).
2. In the Home ribbon, click New measure.
3. The formula bar will appear again. Enter the following DAX formula inside:

Ideal Burndown = 
VAR TotalSprintDays = 
    COUNTROWS(ALL('SprintData'[Date]))
VAR TotalScope =
    MAX('SprintData'[Total Points]) 
VAR DailyBurnRate = 
    DIVIDE(TotalScope, TotalSprintDays)
VAR DaysPassed = 
    COUNTROWS(
        FILTER(
            ALL('SprintData'[Date]),
            'SprintData'[Date] <= MAX('SprintData'[Date])
        )
    )
VAR IdealRemaining = 
    TotalScope - (DaysPassed * DailyBurnRate)

RETURN
    IF(IdealRemaining < 0, 0, IdealRemaining)

Let's briefly walk through what this formula does:

• VAR helps store variables to make the calculation cleaner.
• TotalSprintDays: Counts all the unique days in your sprint to find the duration.
• TotalScope: Gets the total number of points planned for the sprint.
• DailyBurnRate: Calculates the perfect amount of work to be completed each day by dividing total scope by days.
• DaysPassed: Counts how many days have passed since the sprint's start.
• IdealRemaining: Subtracts the work that should have been done (DaysPassed * DailyBurnRate) from the total scope.
• RETURN: Ensures the remaining work never drops below zero.

Step 4: Build the Visualization

Now that you have your 'Actual Remaining' work and your 'Ideal Burndown' calculation, it's time to build the chart.

1. Click anywhere on the blank report canvas.
2. In the Visualizations pane, select the Line chart icon.
3. Drag the fields from your Fields pane to the visualization's settings:

You should now see two lines on your chart! One represents your ideal progress, and the other shows your team's actual progress.

Step 5: Format Your Chart for Clarity

An unformatted chart can be hard to read, but a few quick tweaks can make it much more professional.

• Rename Lines: In the Visualizations pane, double-click on 'Actual Remaining' and 'Ideal Burndown' under the Y-axis to rename them (e.g., to "Remaining Work" and "Ideal Progress").
• Adjust Colors: Click on the format brush icon ('Format your visual'). Go to the Lines section and then Colors, ensuring you distinguish the two lines clearly. For example, make the Ideal Burndown line a subtle grey or black and your Actual line a vibrant color like blue or green.
• Modify the Ideal Line Style: You can change the style of the ideal line to be dashed under the Lines formatting options, visually separating it as a guide.
• Add a Title and Axis Labels: In the General tab, turn on a descriptive Title. Then, go to the X-Axis and Y-Axis sections to add clear labels like "Sprint Date" and "Story Points Remaining."

How to Interpret Your Burndown Chart

Building the chart is one thing, using it to drive action is another. Here’s how to read the story your data is telling you:

• Actual Line is Below the Ideal Line: This is a sign that your team is ahead of schedule. Keep doing what you're doing, but also use it as a chance to ask why. Did the team estimate well? Are they working at a sustainable pace?
• Actual Line is Above the Ideal Line: This means your team is behind schedule. This isn't a cue for blame, it's a prompt for conversation. The chart immediately highlights this trend, allowing the team to discuss potential blockers or reassess estimates during the daily scrum.
• Actual Line Grows Up: If your remaining work increases, it’s a sign of scope creep – new tasks were added to the sprint after it started. Burndown charts make this immediately visible to everyone and can help teams protect their sprint commitment.
• Actual Line is Flat for Several Days: This often signals a blocker. If no work is being completed for multiple days, the team is likely stuck on a problem and may need help to get moving again. The chart makes this stall impossible to ignore.

Final Thoughts

Creating a burndown chart in Power BI transforms rows of project data into a powerful, at-a-glance story about your team's progress. By combining a few simple DAX formulas and clear visualization practices, you can build a reporting tool that informs teams, sparks important conversations, and keeps projects moving toward their goals.

We believe that understanding your data should be straightforward. With Graphed, our mission is to eliminate the complexity of report building. Instead of learning DAX and a new visualization pane, you can connect your data sources — like project boards or databases — and build the report you need by simply describing what you want. For example, you could say, "Create a burndown chart for our latest sprint from Jira," and get a real-time answer in seconds, spending your time analyzing team performance instead of building reports.