Waterfall charts in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a retail company.

📋 Request: Your manager wants to understand how different factors contributed to the change in net profit from last year to this year. They ask you to create a waterfall chart to visualize the step-by-step impact of revenue, costs, and expenses on net profit.

📊 Data: You have a dataset with the following columns: Category (Revenue, Cost of Goods Sold, Operating Expenses, Taxes, Net Profit), Amount (positive or negative values representing changes), and Year (Last Year, This Year).

🎯 Deliverable: Create a waterfall chart in Power BI that shows the starting net profit from last year, the positive and negative contributions of each category, and the final net profit for this year.

Sample Data

Category	Amount	Year
Net Profit	50000	Last Year
Revenue	70000	This Year
Cost of Goods Sold	-30000	This Year
Operating Expenses	-15000	This Year
Taxes	-5000	This Year
Net Profit	70000	This Year

1

Step 1: Filter the data to include only the 'Last Year' net profit and the 'This Year' categories except the final net profit.

Use Power BI filter pane to select Year = 'Last Year' and Category = 'Net Profit' OR Year = 'This Year' and Category <> 'Net Profit'.

Expected Result

Filtered data with starting net profit and all contributing categories for this year.

2

Step 2: Create a new calculated table to prepare data for the waterfall chart with columns: Category, Amount, and SortOrder.

Use DAX: WaterfallData = UNION( ROW("Category", "Starting Net Profit", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "Last Year", Table[Category] = "Net Profit"), "SortOrder", 1), ROW("Category", "Revenue", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "This Year", Table[Category] = "Revenue"), "SortOrder", 2), ROW("Category", "Cost of Goods Sold", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "This Year", Table[Category] = "Cost of Goods Sold"), "SortOrder", 3), ROW("Category", "Operating Expenses", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "This Year", Table[Category] = "Operating Expenses"), "SortOrder", 4), ROW("Category", "Taxes", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "This Year", Table[Category] = "Taxes"), "SortOrder", 5), ROW("Category", "Ending Net Profit", "Amount", CALCULATE(SUM(Table[Amount]), Table[Year] = "This Year", Table[Category] = "Net Profit"), "SortOrder", 6) )

Expected Result

A table with ordered categories and their amounts ready for the waterfall chart.

3

Step 3: Add the new calculated table as the data source for the waterfall chart visual in Power BI.

Insert Waterfall chart visual, set Category field to 'Category', set Y-axis to 'Amount', sort by 'SortOrder', and in the Format pane > Waterfall chart > Breakdown, set 'Starting Net Profit' and 'Ending Net Profit' to 'Total'.

Expected Result

Waterfall chart showing the starting net profit, positive and negative contributions, and ending net profit.

4

Step 4: Format the waterfall chart for clarity: add data labels, set colors for increases (green) and decreases (red), and add a chart title.

In visual formatting pane, enable data labels, set increase color to green, decrease color to red, and title to 'Net Profit Change Waterfall'.

Expected Result

Clear and visually distinct waterfall chart that is easy to interpret.

Final Result

Net Profit Change Waterfall

Starting Net Profit  |████████████████████| 50,000
Revenue             |███████████████████████████████| +70,000
Cost of Goods Sold  |███████████████           | -30,000
Operating Expenses  |█████████                 | -15,000
Taxes               |███                       | -5,000
Ending Net Profit   |██████████████████████████| 70,000

✓Net profit increased from 50,000 last year to 70,000 this year.

✓Revenue contributed a positive 70,000 increase.

✓Cost of Goods Sold, Operating Expenses, and Taxes reduced profit by 30,000, 15,000, and 5,000 respectively.

✓Overall, the company improved profitability despite costs.

Bonus Challenge

Create a dynamic waterfall chart that allows the user to select different years to compare net profit changes.

Show Hint

Use a slicer for Year selection and modify the calculated table to use selected years dynamically with DAX functions like SELECTEDVALUE.

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