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

 

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

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