Power BI & DAX: How to Make Waterfall Charts Work (showing starting and ending values)

If you have tried to use the default Power BI waterfall chart to recreate your PowerPoint variance bridge but got frustrated because you were not able to show from and to values along with your variances, then you are in luck, because in this video I will show you how you can build any variance bridge in power bi and make it look the way you would expect it to.

Most waterfall charts that you see in Power BI look like this:

In other words, you can see the total variance (1.4M) in the chart above, and also the breakdown of that variances along the selected category., However, you are not able to see the starting and ending values like you can in the chart below:

In my video I go over the steps to make the standard Power BI Waterfall chart do what you would expect it to do out of the box. Please see the DAX below for additional details:

Original Margin PVM

PVM (Margin) =
SWITCH (
SELECTEDVALUE ( PVM[PVM] ),
“Price”, [PVM Margin – Price Impact],
“Volume”, [PVM Margin – Volume Impact],
“Mix”, [PVM Margin – Mix Impact],
BLANK ()
)

Adjusted Margin PVM

PVM (Margin) Bridge =
SWITCH (
    SELECTEDVALUE ( Walk[WalkSort] ),
    1SWITCH (
        SELECTEDVALUE ( PVM[PVM] ),
        “Price”-1 * [PVM Margin – Price Impact],
        “Volume”-1 * [PVM Margin – Volume Impact],
        “Mix”-1 * [PVM Margin – Mix Impact],
        [Margin LY]
    ),
    2SWITCH (
        SELECTEDVALUE ( PVM[PVM] ),
        “Price”0,
        “Volume”0,
        “Mix”0,
        [Margin]
    )
)

Walk table:

WalkStart

WalkSort

LY 1
Actual 2

Category (or PVM) table:

PVM

Order

Price 1
Volume 2
Mix 3
New Products 4
Discontinued Products 5

 

23 thoughts on “Power BI & DAX: How to Make Waterfall Charts Work (showing starting and ending values)

  1. Do the 2 tables Walkstart andPVM have to be linked to the table where you have the values through relations?

  2. I have 5 packets in the middle (Price, Volume, ..ect),
    I check the packet formula in matrix table and they are correct,
    I followed the same steps and dax formula but what happened, the beginning total column takes the (price) pocket value, not the beginning value I defined in [switch 1], then the first packet takes the price, and then 0 for last total column, the 4 pockets not showing and the last column shows 0 not the end balance I defined in [switch 2]??

    I don’t know why, can you please help?

  3. Hello, and thank you for sharing this very interesting find. How to order with the CategoryOrder variable? My bridge is ordered according to the % evolution of each category and not according to the order of the defined categories.

    1. I did it according to your example, but my chart is not sorting by category order. he is ordering by value. what can be wrong?

  4. Exactly what I was looking for! After spending a significant amount of time trying to get this to work – this approach was spot on! Thank you!

  5. Thanks for the excellent tutorial! The legend now shows a new entry ‘Other’, would you know how to get rid of it?

  6. I have gone through your vide and implemented waterfall chart but my current column (last of chart) is not getting displayed completely and its been shown as relative only. could you please help.

  7. Thank you for the tutorial, I managed to implement it. However I have one small issue that my breakdown categories (Volume and price) don’t always show up in the same order. My charts places the bigger value at the beginning and I would like to force the volume to always show at the 1st breakdown place

  8. Back again – the waterfall works well with values but seems to have trouble breaking down percentages. For example: if I own a percentage of houses in a market that includes multiple states and I want to see which state has contributed +/- to my overall percentage from a previous period to current period – the % returned by the breakdown is bound to the change for each state, not the % change against the total. For Example: Prior period I have 50% and now I have 55% of the total market – The visual instead of breaking down the 5% +/- contribution per state the visual is returning the % change within each state which often exceeds the 5% total change. Any ideas on what I might be missing or is this even possible?

  9. Is there a way to have the packets as a hierarchy? I tried adding something like “Country > State > City” in the breakdown but Power BI does not allows it. I tried changing Category for Breakdown, but the result looks different. If you know anything let me know, please!

    Thanks for your posts about waterfalls, they have been really helpful.

  10. As stated above, exactly what I needed, thank you. However, as I do not understand exactly how this works, I cannot fix the issue that came up. The Original code gets it right but the Adjusted one gives the following:
    1. Start and End positions are correct
    2. All effects in the middle are the same (equal to total difference between Start and End)
    The code seems exactly the same as the one you are showing but apparently not. Anyone can help?
    Thank you!

    Waterfall Bridge =

    Var VolumeEffect = SUM(‘ALL Effects'[Gross Sales Volume])

    Var MixEffect = SUM(‘ALL Effects'[Gross Sales Mix])

    Var PriceEffect = SUM(‘ALL Effects'[Gross Sales Price])

    Var FXEffect = SUM(‘ALL Effects'[Gross Sales FX])

    RETURN
    SWITCH(SELECTEDVALUE(‘Start-End'[WalkOrder]),
    1,
    SWITCH(SELECTEDVALUE(Index[Effect]),
    “Price”, VolumeEffect,
    “Mix”, MixEffect,
    “Price”, PriceEffect,
    “FX”, FXEffect,
    SUM(BASE[GSR])
    ),
    2,
    SWITCH(SELECTEDVALUE(Index[Effect]),
    “Price”, 0,
    “Mix”, 0,
    “Price”, 0,
    “FX”, 0,
    SUM(NEW[GSR])
    ))

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s