Waterfall chart with multiple measures

Hi Krishna1826 ,

To create a waterfall chart with multiple reset points in Power BI, you need to build a "faux waterfall" using a stacked column chart, as this gives you the necessary control. The process involves creating a custom table to define the chart's structure and then using a few DAX measures to position each bar correctly.

First, you'll need to create a disconnected table to act as a blueprint for the visual. You can do this using the Enter Data feature. This table should have three columns: Category (for the measure names), Index (from 1 to 13 to set the order), and Group (to define the reset points). For your requirement, Measures 1-5 would be Group 1, Measures 6-10 would be Group 2, Measures 11-12 would be Group 3, and Measure 13 would be Group 4. After naming this table Waterfall Structure, it's crucial to go to the Data view and sort the Category column by the Index column to ensure the chart displays in the correct sequence.

With the structure in place, you will create three DAX measures. The first is a simple mapper that returns the value for the current category.

Waterfall Value =
SWITCH(
    SELECTEDVALUE('Waterfall Structure'[Category]),
    "Measure 1",  [YourMeasure1],
    "Measure 2",  [YourMeasure2],
    "Measure 3",  [YourMeasure3],
    "Measure 4",  [YourMeasure4],
    "Measure 5",  [YourMeasure5],
    "Measure 6",  [YourMeasure6],
    "Measure 7",  [YourMeasure7],
    "Measure 8",  [YourMeasure8],
    "Measure 9",  [YourMeasure9],
    "Measure 10", [YourMeasure10],
    "Measure 11", [YourMeasure11],
    "Measure 12", [YourMeasure12],
    "Measure 13", [YourMeasure13]
)

The second measure is the most important; it calculates the invisible base for each column. This measure computes a running total of the [Waterfall Value] but resets for each new group defined in your structure table. This is what creates the "step" effect within each segment and starts new segments at zero.

BaseOffset =
VAR CurrentIndex = SELECTEDVALUE('Waterfall Structure'[Index])
VAR CurrentGroup = SELECTEDVALUE('Waterfall Structure'[Group])
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                'Waterfall Structure',
                'Waterfall Structure'[Group] = CurrentGroup &&
                'Waterfall Structure'[Index] < CurrentIndex
            ),
            [Waterfall Value]
        ),
        ALLSELECTED('Waterfall Structure')
    )

The final measure simply represents the visible part of the bar, which is the actual value for that category.

Delta = [Waterfall Value]

To build the visual, add a Stacked column chart to your report. Place the Category column on the X-axis and drag both the [BaseOffset] and [Delta] measures to the Y-axis. In the formatting options for the visual, find the color settings for the columns and set the transparency of the BaseOffset series to 100%, making it completely invisible. You can then color the Delta series as needed. Finally, enable data labels but configure them to show only for the Delta series, ensuring that only the meaningful change values are displayed on your chart.

Best regards,