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.

Practice

1. What is the main purpose of a Waterfall chart in Power BI?

easy

A. To compare two unrelated categories side by side

B. To show how individual values add or subtract to reach a total

C. To display data in a pie format

D. To show only positive values over time

Solution

1. Step 1: Understand Waterfall chart purpose

   Waterfall charts visualize how starting values change step-by-step by additions or subtractions to reach an end value.

2. Step 2: Compare options with this purpose

   Only To show how individual values add or subtract to reach a total describes this stepwise addition and subtraction clearly.

3. Final Answer:

   To show how individual values add or subtract to reach a total -> Option B

4. Quick Check:

   Waterfall charts = stepwise changes [OK]

Hint: Think: How does value move from start to end? [OK]

Common Mistakes:

• Confusing Waterfall with Pie chart
• Thinking it only shows positive values
• Assuming it compares unrelated categories

2. Which of the following is the correct way to assign fields when creating a Waterfall chart in Power BI?

easy

A. Assign 'Date' to the legend and 'Category' to the values field

B. Assign 'Value' to the axis and 'Category' to the values field

C. Assign 'Category' to the axis and 'Value' to the values field

D. Assign 'Category' to the tooltip and 'Value' to the legend

Solution

1. Step 1: Identify correct field roles for Waterfall chart

   The category field defines the steps on the axis, and the value field shows the amount added or subtracted.

2. Step 2: Match options to correct roles

   Assign 'Category' to the axis and 'Value' to the values field correctly assigns 'Category' to axis and 'Value' to values field, which is required for Waterfall charts.

3. Final Answer:

   Assign 'Category' to the axis and 'Value' to the values field -> Option C

4. Quick Check:

   Category = axis, Value = values [OK]

Hint: Category goes on axis, values show changes [OK]

Common Mistakes:

• Swapping category and value fields
• Using date or legend incorrectly
• Assigning category to tooltip instead of axis

3. Given the following data:

Category | Value
Start    | 100
Sales    | 50
Returns  | -20
Expenses | -30
End      | ?

What will be the final 'End' value shown in the Waterfall chart?

medium

A. 100 + 50 - 20 - 30 = 100

B. 100

C. 100 + 50 - 20 - 30 = 100 but End is 0

D. 100 + 50 - 20 - 30 = 100 but End is 100

Solution

1. Step 1: Calculate stepwise changes

   Start at 100, add 50 (Sales) -> 150, subtract 20 (Returns) -> 130, subtract 30 (Expenses) -> 100.

2. Step 2: Determine final End value

   The End value is the total after all changes, which is 100.

3. Final Answer:

   100 + 50 - 20 - 30 = 100 -> Option A

4. Quick Check:

   Sum changes = 100 [OK]

Hint: Add positives, subtract negatives stepwise [OK]

Common Mistakes:

• Ignoring negative values as subtractions
• Assuming End is zero by default
• Adding values incorrectly

4. You created a Waterfall chart but it shows all bars as positive values, even though some values are negative. What is the most likely cause?

medium

A. You applied a filter that removes negative values

B. You assigned the 'Value' field as text instead of number

C. You forgot to add the 'Category' field to the axis

D. You used a measure that returns absolute values instead of signed values

Solution

1. Step 1: Analyze why negatives appear positive

   If the measure returns absolute values, negatives become positive, causing all bars to show as positive.

2. Step 2: Check other options

   Other options don't match: assigning values as text would prevent numerical display or cause errors, missing category on axis would disrupt chart structure, a filter removing negatives would eliminate those bars entirely rather than showing them as positive.

3. Final Answer:

   You used a measure that returns absolute values instead of signed values -> Option D

4. Quick Check:

   Absolute values hide negatives [OK]

Hint: Check if measure uses ABS() or similar function [OK]

Common Mistakes:

• Confusing data type errors with value sign issues
• Ignoring measure logic
• Assuming missing category causes positive bars

5. You want to create a Waterfall chart showing monthly profit changes, but your data has some months missing. How should you prepare your data to ensure the Waterfall chart shows all months in order with correct step changes?

hard

A. Fill missing months with zero profit and sort by month

B. Remove months with no data to avoid confusion

C. Use a pie chart instead to avoid missing months

D. Manually add missing months as negative values

Solution

1. Step 1: Understand missing data impact on Waterfall chart

   Missing months cause gaps in the axis and incorrect step calculations.

2. Step 2: Prepare data by filling missing months

   Fill missing months with zero profit so the chart shows continuous months and correct cumulative changes.

3. Step 3: Sort data by month

   Sorting ensures the Waterfall steps follow the correct time order.

4. Final Answer:

   Fill missing months with zero profit and sort by month -> Option A

5. Quick Check:

   Fill gaps with zero and sort [OK]

Hint: Fill missing steps with zero to keep continuity [OK]

Common Mistakes:

• Removing missing months causing gaps
• Using wrong chart type
• Adding fake negative values