Waterfall charts in Power BI - Deep Dive

Overview - Waterfall charts

What is it?

A waterfall chart is a type of data visualization that shows how an initial value changes through a series of positive and negative values, leading to a final result. It helps you see the cumulative effect of sequentially introduced values, like profits and losses over time. Each bar in the chart represents a step that either adds to or subtracts from the total. This makes it easy to understand how individual parts contribute to the whole.

Why it matters

Waterfall charts exist to make complex changes easy to understand at a glance. Without them, you might only see the start and end numbers, missing the story of what caused the change. This can lead to confusion or wrong decisions because you don’t know which parts had the biggest impact. Waterfall charts help businesses track financial results, project progress, or any process where values build up or break down step-by-step.

Where it fits

Before learning waterfall charts, you should understand basic chart types like bar and column charts and know how to read simple data tables. After mastering waterfall charts, you can explore more advanced visualizations like combo charts or custom visuals in Power BI, and learn to create dynamic measures with DAX to feed these charts.

Mental Model

Core Idea

A waterfall chart visually breaks down how an initial value changes step-by-step through additions and subtractions to reach a final total.

Think of it like...

Imagine filling a glass with water, then pouring some out, then adding more, step by step. The water level rises and falls, showing the effect of each action until you see the final amount left.

Initial Value ─┐
               │
    + Step 1 ──┼─> Intermediate Value ─┐
    - Step 2 ──┼──────────────────────┼─> Final Value
    + Step 3 ──┘                      │
                                    └─> Total Change

Build-Up - 7 Steps

1

Foundation

Understanding basic bar charts

Concept: Learn what bar charts are and how they display data as vertical or horizontal bars.

Bar charts show quantities by the length of bars. Each bar represents a category or time period. Taller bars mean bigger values. This is the foundation for understanding waterfall charts, which build on bars but add a running total effect.

Result

You can read simple bar charts and compare values visually.

Knowing how bars represent values helps you grasp how waterfall charts use bars to show changes step-by-step.

2

Foundation

Reading cumulative totals in data

Concept: Understand how adding and subtracting values changes a running total.

Imagine you start with 100 dollars. If you earn 20, your total is 120. If you spend 30, your total drops to 90. Tracking these changes in a list helps you see how the total moves up and down.

Result

You can follow how a total changes with each addition or subtraction.

Seeing cumulative totals in numbers prepares you to visualize them in a waterfall chart.

3

Intermediate

Building a waterfall chart step-by-step

🤔Before reading on: do you think each bar in a waterfall chart stands alone or connects to the previous one? Commit to your answer.

Concept: Learn how each bar in a waterfall chart represents a change that starts where the last bar ended.

In a waterfall chart, the first bar shows the starting value. Each next bar shows a positive or negative change. Bars float above or below the baseline to show increases or decreases. The final bar shows the ending total. This connection between bars creates a visual flow.

Result

You can explain how a waterfall chart builds from start to finish.

Understanding the connected nature of bars reveals why waterfall charts tell a clear story of change.

4

Intermediate

Using waterfall charts in Power BI

🤔Before reading on: do you think Power BI automatically calculates running totals for waterfall charts, or do you need to prepare data first? Commit to your answer.

Concept: Learn how Power BI creates waterfall charts and what data it needs.

Power BI has a built-in waterfall chart visual. You provide a category field (like months or expense types) and a value field (positive or negative numbers). Power BI calculates the running total and draws the bars accordingly. You can customize colors for increases, decreases, and totals.

Result

You can create a basic waterfall chart in Power BI from your data.

Knowing Power BI’s automatic calculation saves time and avoids manual errors.

5

Intermediate

Customizing waterfall chart colors and labels

Concept: Learn how to make waterfall charts clearer by adjusting colors and adding labels.

In Power BI, you can set colors for positive changes (usually green), negative changes (usually red), and totals (often blue or gray). Adding data labels on bars shows exact numbers. This helps viewers quickly understand which steps add or subtract and by how much.

Result

Your waterfall chart becomes easier to read and interpret.

Visual cues like color and labels guide the viewer’s attention and improve communication.

6

Advanced

Handling subtotals and breaks in waterfall charts

🤔Before reading on: do you think subtotals in waterfall charts are just normal bars or special bars? Commit to your answer.

Concept: Learn how to show intermediate totals or breaks in the sequence within a waterfall chart.

Sometimes you want to show subtotals, like total revenue before expenses. Power BI lets you mark bars as totals or subtotals, which float on the baseline and don’t add or subtract. This clarifies the structure and helps viewers see groupings of changes.

Result

You can create waterfall charts that show logical groupings and intermediate totals.

Recognizing subtotals visually helps prevent confusion about how values combine.

7

Expert

Advanced DAX for dynamic waterfall charts

🤔Before reading on: do you think you can create dynamic waterfall charts that respond to slicers without extra DAX? Commit to your answer.

Concept: Learn how to write DAX measures that calculate running totals dynamically for interactive waterfall charts.

Using DAX, you can create measures that calculate cumulative sums based on filters or slicers. This allows your waterfall chart to update automatically when users select different time periods or categories. Techniques include using CALCULATE, FILTER, and variables to control context and order of calculation.

Result

Your waterfall charts become interactive and adapt to user input.

Mastering DAX for running totals unlocks powerful, flexible visualizations beyond static charts.

Under the Hood

Waterfall charts work by calculating a running total that starts from an initial value and adds or subtracts each subsequent value in order. Internally, Power BI uses the data categories to order the steps and sums the values cumulatively. Bars are drawn floating above or below a baseline to represent positive or negative changes. Totals and subtotals are treated as fixed points resetting the running total display.

Why designed this way?

Waterfall charts were designed to clearly show how individual changes contribute to a total, solving the problem of understanding complex sequences of increases and decreases. The floating bar design visually separates positive and negative impacts, making the flow of changes intuitive. Alternatives like stacked bars or line charts do not show the stepwise buildup as clearly.

┌───────────────┐
│ Initial Value │
└──────┬────────┘
       │
  ┌────▼────┐
  │ Change 1│ (+ or -)
  └────┬────┘
       │
  ┌────▼────┐
  │ Change 2│ (+ or -)
  └────┬────┘
       │
  ┌────▼────┐
  │  ...    │
  └────┬────┘
       │
  ┌────▼────┐
  │ Final   │
  │ Total   │
  └─────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think waterfall charts only show positive changes? Commit to yes or no.

Common Belief: Waterfall charts only display positive values or gains.

Reality: Waterfall charts show both positive and negative changes, clearly distinguishing increases and decreases.

Why it matters: Believing they only show gains can cause you to miss losses or expenses, leading to incomplete analysis.

Quick: Do you think the order of categories in a waterfall chart does not affect the result? Commit to yes or no.

Common Belief: The order of steps in a waterfall chart does not matter; the total will be the same anyway.

Reality: The order matters because the running total changes step-by-step; changing order can confuse the story or mislead viewers.

Why it matters: Ignoring order can produce misleading visuals that hide the true sequence of changes.

Quick: Do you think subtotals in waterfall charts are just normal bars? Commit to yes or no.

Common Belief: Subtotals are just regular bars that add or subtract values like others.

Reality: Subtotals are special bars that represent intermediate totals and do not add or subtract; they reset the baseline visually.

Why it matters: Treating subtotals as normal bars can confuse viewers and distort the cumulative story.

Quick: Do you think Power BI requires manual calculation of running totals for waterfall charts? Commit to yes or no.

Common Belief: You must manually calculate running totals before creating a waterfall chart in Power BI.

Reality: Power BI automatically calculates running totals for waterfall charts if data is structured correctly.

Why it matters: Thinking manual calculation is needed wastes time and complicates reports unnecessarily.

Expert Zone

1

Waterfall charts can be sensitive to data sorting; explicit sorting by date or category is crucial to maintain the correct sequence.

2

Using DAX to create dynamic running totals allows waterfall charts to respond to filters and slicers, enabling interactive dashboards.

3

Subtotals and totals in waterfall charts are visually distinct and require special handling in data or visual settings to avoid misinterpretation.

When NOT to use

Waterfall charts are not ideal when you have many small changes that clutter the chart or when changes are not sequential. In such cases, line charts or stacked area charts may better show trends or proportions over time.

Production Patterns

In real-world Power BI reports, waterfall charts are often used for financial statements, showing profit and loss breakdowns, or project status reports to visualize budget changes. Experts combine waterfall charts with slicers and dynamic measures to create interactive, user-driven insights.

Connections

Running totals

Waterfall charts build on the concept of running totals by visually representing them step-by-step.

Understanding running totals mathematically helps grasp how waterfall charts accumulate values visually.

Financial accounting

Waterfall charts are widely used in financial accounting to break down income statements and cash flows.

Knowing accounting principles clarifies why waterfall charts highlight gains, losses, and subtotals.

Project management

Waterfall charts relate to project management by showing how tasks or costs accumulate or reduce over phases.

Seeing waterfall charts as progress trackers helps project managers communicate status clearly.

Common Pitfalls

#1 Using unordered categories causing confusing charts

Wrong approach:

Creating a waterfall chart with categories sorted alphabetically instead of by time or logical sequence.

Correct approach:

Sort categories explicitly by date or logical order before creating the waterfall chart.

Root cause:

Not understanding that the order of steps affects the running total and visual story.

#2 Treating subtotals as normal bars

Wrong approach:

Marking subtotal bars as regular data points that add or subtract values.

Correct approach:

Use the 'Breakdown' or 'Total' setting in Power BI to mark subtotal bars properly so they float on the baseline.

Root cause:

Misunderstanding the visual role of subtotals in the cumulative flow.

#3 Manually calculating running totals outside Power BI unnecessarily

Wrong approach:

Pre-calculating cumulative sums in Excel or SQL before importing data for waterfall charts.

Correct approach:

Provide raw positive and negative values and let Power BI calculate running totals automatically.

Root cause:

Not knowing Power BI’s built-in running total calculation for waterfall charts.

Key Takeaways

Waterfall charts visually explain how an initial value changes step-by-step through additions and subtractions to reach a final total.

The order of categories in a waterfall chart is critical because it controls the sequence of changes and the running total.

Power BI automatically calculates running totals for waterfall charts, simplifying report creation when data is structured correctly.

Subtotals and totals are special bars that reset or anchor the running total visually and must be handled distinctly.

Advanced users can leverage DAX to create dynamic, interactive waterfall charts that respond to user filters and slicers.