Waterfall chart with multiple measures

[Krishna1826]

I have 13 measures  which i want to show in waterfall chart but the way i want to display is tricky which is what i am not able to figure it out. Kindly help me

Measures:

Measure 1

Measure 2

Measure 3

Measure 4'

Measure 5

Measure 6

Measure 7

Measure 8

Measure 9

Measure 10

Measure 11

Measure 12

Measure 13

I want Measure 1 start from 0 then measure 2 to 5, then measure 6 from 0 then measure 7 to 10, measure 11 from 0 then measure 12 then measure 13 again from 0. this is what typical client requirment.

I tried one blog post but my scenario is completely different, I created two disconnecetd tables one with measure1,6,11,13 and another with rest of measure 

https://businessintelligist.com/2020/06/12/power-bi-dax-how-to-make-waterfall-charts-work-showing-st...

[v-dineshya · Accepted solution]

Hi @Krishna1826 ,

Thank you for reaching out to the Microsoft Community Forum.

You have 13 measures, and you want a single waterfall chart where some measures start fresh from zero and others continue cumulatively. This is not directly supported by Power BI native waterfall, but it work using a disconnected table.

Please follow below steps.

1. Created disconnected table with sample data.

2. Created Below Measure .

Waterfall Value =

VAR CurrentOrder = SELECTEDVALUE(MeasureOrder[Order])

VAR CurrentGroup = SELECTEDVALUE(MeasureOrder[Group])

VAR RunningTotal =

    CALCULATE(

        SUMX(

            FILTER(

                MeasureOrder,

                MeasureOrder[Order] <= CurrentOrder &&

                MeasureOrder[Group] = CurrentGroup

            ),

            MeasureOrder[Value]

        )

    )

RETURN

RunningTotal

3. Please refer below Output snaps and attached PBIX file

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Krishna1826 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Krishna1826 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Nasif_Azam]

Hey @Krishna1826 ,

Try tge following steps:

1) Create a Helper Table: Create a table with columns for Step, Measure, Value, and IsStart to manage the flow and resets for each set of measures.

2) Create a DAX Measure:  Use a DAX measure to calculate the Cumulative Value. Reset the value to 0 when IsStart = TRUE and accumulate the values for subsequent measures.

Cumulative Total = 
IF(
   VALUES('HelperWaterfall'[IsStart]) = TRUE(), 
   0, 
   CALCULATE(
     SUM('HelperWaterfall'[Value]), 
     FILTER(
       ALL('HelperWaterfall'), 
       'HelperWaterfall'[Step] <= MAX('HelperWaterfall'[Step])
     )
   )
)

3) Build the Waterfall Chart:

Please also check the Watefall Chart.pbix file. If there any queries let me know.

Best Regards,
Nasif Azam

[DataNinja777]

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,

[Krishna1826]

Thanks for your quick reply, I tried the logic but even the first one didnot start from 0 and same with other also, I laso need different group like blue, green , red as shown in pic , if you have tried kindly share the file or measure