Auto Adjust Breakdown Number in Waterfall Charts

[Anonymous]

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!

[Shravan133 · Accepted 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.

1. 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.

1. 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.

1. 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

1. Add a waterfall chart to your report.
2. Set the Y-axis to Revenue.
3. Set the X-axis category to Year.
4. Set the Breakdown to Customer.
5. Use the measure TopNRevenueChange to highlight the top 5 customers.

Step 5: Configure the Visual

1. 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.
2. 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.