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.