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

 

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

  1. 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?

  2. 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?

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