Forum Discussion
Auto Adjust Breakdown Number in Waterfall Charts
Hello,
I am trying to build a waterfall chart showing Revenue as Y-axis, with Years as the X-axis category and Customer as breakdown. In the breakdown, I hope to highlight major customers that are driving each of the yearly change in Revenue, with a maximum breakdown of 5.
Currently, I am able to set the maximum breakdown to 5 in the visual panel. However, this doesn't seem to be a "maximum" but is instead a fixed count, where the graph ALWAYS displays 5 breakdowns even when some of the breakdown = 0.
Is it possible to set the maximum breakdown to 5, while ensuring that none of the breakdown = 0? I want to highlight at most 5 customers that are key to the change in revenue, but highlight fewer ones if there is only <5 customers contributing to the change.
Thank you. Really appreciate your help!
To achieve a waterfall chart in Power BI that shows Revenue as the Y-axis, Years as the X-axis category, and Customers as the breakdown while ensuring that the breakdowns do not include zero values and display at most 5 significant customers, you can follow these steps:
Step 1: Create a Calculated Column for Revenue Change
First, you need to create a calculated column to calculate the yearly change in revenue for each customer. This helps identify the significant customers contributing to the change in revenue.
- Create a calculated column:
Revenue Change =
VAR CurrentYearRevenue = SUM('RevenueTable'[Revenue])
VAR PreviousYearRevenue = CALCULATE(
SUM('RevenueTable'[Revenue]),
FILTER('RevenueTable', 'RevenueTable'[Year] = EARLIER('RevenueTable'[Year]) - 1)
)
RETURN CurrentYearRevenue - PreviousYearRevenue
Step 2: Filter Out Zero Revenue Change Customers
Create a measure to filter out customers with zero revenue change.
- Create a measure to filter customers with non-zero revenue change:
NonZeroRevenueChange =
IF([Revenue Change] <> 0, [Revenue Change], BLANK())
Step 3: Create a Measure for Top N Customers
Create a measure to dynamically filter the top N customers based on the revenue change.
- Create a measure for the Top N customers:
TopNRevenueChange =
VAR TopNCustomers =
TOPN(
5,
SUMMARIZE(
'RevenueTable',
'RevenueTable'[Customer],
"RevenueChange", [Revenue Change]
),
[RevenueChange], DESC
)
RETURN
IF(
'RevenueTable'[Customer] IN TopNCustomers,
[Revenue Change],
BLANK()
)
Step 4: Create the Waterfall Chart
- Add a waterfall chart to your report.
- Set the Y-axis to Revenue.
- Set the X-axis category to Year.
- Set the Breakdown to Customer.
- Use the measure TopNRevenueChange to highlight the top 5 customers.
Step 5: Configure the Visual
- In the Visualizations pane:
- Add the Year to the X-axis.
- Add the Revenue to the Y-axis.
- Add the Customer to the Breakdown.
- Add the TopNRevenueChange to the Values.
- Configure the Maximum Breakdown:
- In the Breakdown settings, set the maximum breakdown to 5.
- Ensure that the measure TopNRevenueChange is used to highlight the top 5 customers.
By following these steps, you ensure that the waterfall chart dynamically adjusts to show at most 5 significant customers that contribute to the yearly change in revenue. If there are fewer than 5 customers contributing to the change, the chart will only display those customers, excluding any with zero revenue change.
1 Reply
To achieve a waterfall chart in Power BI that shows Revenue as the Y-axis, Years as the X-axis category, and Customers as the breakdown while ensuring that the breakdowns do not include zero values and display at most 5 significant customers, you can follow these steps:
Step 1: Create a Calculated Column for Revenue Change
First, you need to create a calculated column to calculate the yearly change in revenue for each customer. This helps identify the significant customers contributing to the change in revenue.
- Create a calculated column:
Revenue Change =
VAR CurrentYearRevenue = SUM('RevenueTable'[Revenue])
VAR PreviousYearRevenue = CALCULATE(
SUM('RevenueTable'[Revenue]),
FILTER('RevenueTable', 'RevenueTable'[Year] = EARLIER('RevenueTable'[Year]) - 1)
)
RETURN CurrentYearRevenue - PreviousYearRevenue
Step 2: Filter Out Zero Revenue Change Customers
Create a measure to filter out customers with zero revenue change.
- Create a measure to filter customers with non-zero revenue change:
NonZeroRevenueChange =
IF([Revenue Change] <> 0, [Revenue Change], BLANK())
Step 3: Create a Measure for Top N Customers
Create a measure to dynamically filter the top N customers based on the revenue change.
- Create a measure for the Top N customers:
TopNRevenueChange =
VAR TopNCustomers =
TOPN(
5,
SUMMARIZE(
'RevenueTable',
'RevenueTable'[Customer],
"RevenueChange", [Revenue Change]
),
[RevenueChange], DESC
)
RETURN
IF(
'RevenueTable'[Customer] IN TopNCustomers,
[Revenue Change],
BLANK()
)
Step 4: Create the Waterfall Chart
- Add a waterfall chart to your report.
- Set the Y-axis to Revenue.
- Set the X-axis category to Year.
- Set the Breakdown to Customer.
- Use the measure TopNRevenueChange to highlight the top 5 customers.
Step 5: Configure the Visual
- In the Visualizations pane:
- Add the Year to the X-axis.
- Add the Revenue to the Y-axis.
- Add the Customer to the Breakdown.
- Add the TopNRevenueChange to the Values.
- Configure the Maximum Breakdown:
- In the Breakdown settings, set the maximum breakdown to 5.
- Ensure that the measure TopNRevenueChange is used to highlight the top 5 customers.
By following these steps, you ensure that the waterfall chart dynamically adjusts to show at most 5 significant customers that contribute to the yearly change in revenue. If there are fewer than 5 customers contributing to the change, the chart will only display those customers, excluding any with zero revenue change.