Auto Adjust Breakdown Number in Waterfall Charts

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.