Power BIbi_tool~15 mins

Waterfall charts in Power BI - Real Business Scenario

Choose your learning style9 modes available

Learn Why Deep Sheet Try Challenge Scenario Recall Dash

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.

Progress0 / 4 steps

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

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

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

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

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

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