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

 

29 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])
    ))

  11. This is hands-down one of the best and most useful PBI posts I have ever come across. I would never have arrived upon this solution on my own, and you explained it in very clear, easy steps. The sample code was a life-saver. Excellent work, and thank you!

  12. Hello, thanks for very useful trick for waterfall chart. Just one issue, despite I am using sort by column command in my table for breakdowns I could not sort it in an order that I have in my table. I tried to use chart option of sorting but it sorts with based on values.

  13. Thanks a lot for this! I have created the “switch” measure and that works if I hard code numbers in it or use columns from my tables. If I use measures, it will not display any value, but does not produce an error. If I define variables, it is exactly the same. What am i missing here? I thought I would be able to switch in any value. I want to switch in measures that are calculated based on slicer values, could that be a problem?

Leave a Reply to Power BI Cancel 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