Waterfall charts in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate the correct cumulative total for a Waterfall chart

You have a sales table with daily sales amounts. You want to create a measure that shows the cumulative sales up to each day for a Waterfall chart.

Which DAX measure will correctly calculate the cumulative sales?

A Cumulative Sales = SUM(Sales[Amount]) + PREVIOUSMONTH(Sales[Amount])

B Cumulative Sales = SUMX(FILTER(Sales, Sales[Date] <= EARLIER(Sales[Date])), Sales[Amount])

C Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Date] < MAX(Sales[Date])))

D Cumulative Sales = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))

💡 Hint

Think about how to include all dates up to the current date in the calculation.

Option D uses CALCULATE with FILTER and ALL to remove filters on date and sum all sales up to the current date, which is the correct way to calculate cumulative totals in DAX.

Option D uses EARLIER incorrectly and will cause an error in this context.

Option D excludes the current date by using < instead of <=, so it misses the current day's sales.

Option D uses PREVIOUSMONTH incorrectly and adds a column reference, which is invalid.

❓ visualization

intermediate

Best practice for Waterfall chart color usage

When designing a Waterfall chart, which color scheme is best to clearly show increases, decreases, and totals?

A Use the same color for all bars to keep it simple.

B Use random colors for each bar to make the chart colorful.

C Use green for increases, red for decreases, and gray for totals.

D Use blue for increases, yellow for decreases, and orange for totals.

💡 Hint

Think about common color meanings and how to make the chart easy to understand.

Option C follows common color conventions: green means positive (increase), red means negative (decrease), and gray neutral for totals. This helps users quickly understand the chart.

Options A and C reduce clarity by either confusing colors or no distinction.

Option C uses colors that are less intuitive for increases and decreases.

❓ data_modeling

advanced

Modeling data for Waterfall charts with multiple categories

You want to create a Waterfall chart that shows profit changes by product category and month. Your data has transactions with ProductCategory, Date, Revenue, and Cost columns.

What is the best data modeling approach to support this Waterfall chart?

A Create a Date dimension table, relate it to transactions by Date, and create measures for Profit and cumulative Profit by category and month.

B Use the transactions table only, without any relationships or dimension tables, and create calculated columns for cumulative profit.

C Create a ProductCategory table only and use it to filter transactions, ignoring dates.

D Create a single table combining all data into one flat table with no relationships.

💡 Hint

Think about how to filter and aggregate data by time and category efficiently.

Option A is best because a Date dimension table allows time intelligence functions and filtering by month, while relationships enable correct aggregation by category and date.

Option A lacks a Date table, making time calculations harder and less efficient.

Option A ignores the time dimension, which is needed for monthly Waterfall charts.

Option A loses the benefits of star schema modeling and can cause performance issues.

🔧 Formula Fix

advanced

Identify the error in this Waterfall chart measure

Given this DAX measure for a Waterfall chart cumulative total, what error will it cause?

Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Date] <= MAX(Sales[Date])))

A It will return only the current date's sales, not cumulative.

B It will return the correct cumulative total without errors.

C It will cause a syntax error due to incorrect use of FILTER.

D It will cause a circular dependency error because FILTER references the same table without removing filters.

💡 Hint

Consider how FILTER and CALCULATE interact with row context and filter context.

Option A is correct because FILTER(Sales, ...) iterates only over the Sales rows visible in the current filter context (typically the current date in a date-based visual), so it sums only the current day's sales instead of the cumulative total.

Option A is wrong because the measure does not compute cumulatively.

Option A is wrong because the syntax is valid.

Option A is wrong because there is no circular dependency; the measure evaluates but returns incorrect results.

🧠 Conceptual

expert

Scenario: Explaining Waterfall chart behavior with missing intermediate totals

You created a Waterfall chart showing monthly profit changes. However, the chart skips some months and jumps directly from January to March totals, missing February.

What is the most likely cause of this behavior?

A The data model is missing rows for February, so the Waterfall chart cannot show that month.

B The Waterfall chart automatically hides months with zero or no change, so February is skipped.

C The date column is not sorted correctly, causing months to appear out of order.

D The measure used does not calculate cumulative totals, so intermediate months are ignored.

💡 Hint

Think about how Waterfall charts handle zero or no change values.

Option B is correct because Waterfall charts often hide bars where the value change is zero or missing, causing months with no profit change to be skipped visually.

Option B is less likely because even if data is missing, the axis usually shows all months if a proper Date table is used.

Option B would cause ordering issues but not skip months.

Option B would affect totals but not cause skipping of months.

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