Waterfall chart in Power BI: how to build it

A waterfall chart, also called a bridge chart, shows how you get from a starting value to an end value: each floating column is an increase or decrease, and the last column is the total. In Power BI it is a standard visual in the Visualizations pane. You put a measure on the Y-axis, a field on Category and optionally a field on Breakdown. Building it takes five minutes, the work is in the steps and the data model behind them.

Say the third-quarter result is €183,400 below budget. Everyone at the table sees the number, and the next question follows straight away: where is it?

Lower revenue? Higher purchase prices? The new hires who started earlier than planned?

The answer sits somewhere in a forty-line table, and by the time someone has dug it out, the meeting has moved on. A waterfall chart puts that answer in one picture: budgeted result on the left, actual result on the right, and in between the four or five items that make the difference.

Yet the most commonly built waterfall isn’t the most useful one. The bridge from last year to this year explains what has already happened, and that is looking back. The same chart from budget to the expected year end shows which items you can still steer. That difference decides whether a waterfall sits in a report or in the meeting where decisions are made.

Below you’ll read what a waterfall chart is, when to use it and how to build it in Power BI. You’ll also read two things most explanations leave out: how the standard Power BI visual works without buying an extra visual, and how to set up the data model so the bridge adds up.

What is a waterfall chart?

A waterfall chart is a column chart that takes a change apart. It has three kinds of columns:

• The starting column. Sits on the axis and shows the starting value, for example revenue or the budgeted result.
• The intermediate columns. Float and each show a contribution. An increase starts where the previous column ended and goes up, a decrease goes down.
• The end column. Sits on the axis again and shows the total after all steps.

Because each intermediate column starts exactly where the previous one stops, you read the chart like a staircase going up or down. That is also where the name bridge chart comes from: the chart builds a bridge between two numbers.

A word of caution when searching: not everyone means this chart by waterfall report or waterfall reporting. In project management, waterfall stands for the waterfall model, a plan in consecutive phases. This article is about the chart.

Waterfall chart or bar chart?

At first glance a waterfall chart looks like a bar chart with floating columns. The purpose is different. A bar chart compares: revenue by region, costs by month. Each column starts at zero and stands on its own.

A waterfall explains: it connects a start and end value and shows what was added and taken away along the way.

The difference from a line chart is just as clear. A line shows a trend, such as revenue rising over twelve months with a dip in March. Why March disappointed, the line doesn’t show. A waterfall does, if you choose the steps well.

A useful test: if you want to know how much, choose a bar or line chart. If you want to know why, choose a waterfall.

When should you use a waterfall chart?

Waterfall charts are most common in financial reporting, because the question “how do we get from A to B” keeps coming back there. The best-known uses:

• From revenue to net profit. Revenue, minus cost of goods, minus staff costs, minus other costs, minus depreciation, and you see where the margin goes.
• From budget to actual. Budgeted result, plus or minus the variance per item, ending at the actual result.
• From last year to this year. Last year’s revenue, plus new customers, minus lost customers, plus or minus price effect, ending at this year’s revenue.
• From opening to closing balance. The month’s bank balance, with receipts and payments as steps.

The same shape works for every role at the leadership table, as long as the steps fit the question. The CFO wants to see which cost item pushes margin below budget. The commercial director wants to know whether growth comes from new customers or from higher prices with existing ones. The managing director wants to know which business units make the profit and which eat it.

How to turn those questions into an overview the leadership team uses every month is covered in our article on the management dashboard.

Creating a waterfall chart in Power BI

The waterfall chart is a standard visual in Power BI Desktop and the Power BI service. You don’t need an extra visual for it. According to Microsoft’s documentation it works like this:

1. Pick the visual. Select the waterfall chart in the Visualizations pane. Three wells appear: Category, Breakdown and Y-axis.
2. Put the value on the Y-axis. Use a measure, for example the difference between actual and budgeted revenue.
3. Put the steps on Category. For example the month, the product group or the cost type. Power BI draws a column per category and adds a total at the end automatically.
4. Optionally add a breakdown. With a field on Breakdown, such as region, Power BI splits each step into its largest contributors. The Max breakdowns setting decides how many are visible. The rest is grouped into Other.
5. Set the order. Through More options and Sort axis you sort on the category or on the value.
6. Make it readable. In the format pane, choose the colours for increase, decrease and total, and turn on data labels.

That gives you a working waterfall in five minutes. Whether it explains anything depends on what sits underneath the visual.

The data model behind a good bridge

The standard visual has two properties you account for in the model. It has one total at the end and no subtotals. And it treats every value as a change from zero. For a bridge from budgeted to actual result, you therefore build a small table with the steps and one measure that returns the right value per step:

Bridge value =
SWITCH (
    SELECTEDVALUE ( Bridge[Step] ),
    "Budgeted result", [Result budget],
    "Revenue", [Revenue] - [Revenue budget],
    "Cost of goods", [Cost of goods budget] - [Cost of goods],
    "Staff costs", [Staff costs budget] - [Staff costs],
    "Other costs", [Other costs budget] - [Other costs]
)

Put Bridge[Step] on Category and this DAX measure on the Y-axis. The total at the end is then exactly the actual result. Three things make the difference between a bridge that adds up and one that confuses:

• Signs. For costs, subtract the actual costs from the budgeted ones. Costs above budget then become a decrease, which is how it should feel. Flip it and a cost overrun goes up, and everyone reads it as good news.
• Order. Give the bridge table an Order column and sort the step by that column. Otherwise Power BI puts the steps in alphabetical order and your bridge starts at Cost of goods.
• The starting column. The standard visual draws the budgeted result as an increase from zero, in the colour of an increase. Most readers don’t mind once the axis labels are right. If you want it shown as a total, you need a visual from AppSource.

A bridge is only as good as the numbers behind it. If budget and actuals don’t come from the same chart of accounts, the bridge mostly explains the difference between two classifications. How to set up the financial side of such a model is covered in our article on the financial dashboard.

How to make a waterfall chart readable

The visual is simple, the design needs more attention. These are the agreements that come back in every good waterfall:

• Colour by meaning. One colour for favourable, one for unfavourable and a neutral one for totals. In a cost bridge a decrease is good news, so let the colour follow what it does to the result.
• Labels on every column. With floating columns nobody judges size well. The number next to it prevents debate.
• Five to eight steps. Group small items into Other. A waterfall with thirty steps is no longer a waterfall, it’s a staircase where nobody can see the bottom step.
• A logical order. Follow the structure of the profit and loss account, the sequence in time or the size of the contribution. Pick one and keep it across all reports.
• An axis that starts at zero. For the start and end columns that is the rule. A truncated axis makes small differences look dramatic.

When not to use a waterfall chart

A waterfall only works if the steps add up to a meaningful total. You can add revenue by region up to a total, but then a plain bar chart tells the same story with less explanation. If you want to show a development over twelve months, a line chart is clearer. And if you want to compare categories precisely, the floating base of each column gets in the way.

If you need one bridge for one presentation, you don’t need Power BI for it. Excel has had a built-in waterfall chart since version 2016, in which Set as total also marks a column as a subtotal. Power BI becomes the better choice when the bridge has to refresh every month, filter by department or region, or match the same numbers as the rest of your reporting. A revenue bridge by customer group in a sales dashboard is a good example.

So how is it for you: does your monthly report explain why the result deviates, or only that it deviates? Want to see what such a bridge looks like on your own numbers? Get in touch.

Frequently asked questions