Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
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.
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.
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.
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
Step 5: Configure the Visual
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.
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.
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.
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.
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
Step 5: Configure the Visual
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |