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] ),
1, SWITCH (
SELECTEDVALUE ( PVM[PVM] ),
“Price”, -1 * [PVM Margin – Price Impact],
“Volume”, -1 * [PVM Margin – Volume Impact],
“Mix”, -1 * [PVM Margin – Mix Impact],
[Margin LY]
),
2, SWITCH (
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 |
You need to post sample PBI file which would help us understand better.
I will post it with the next review of the other waterfall chart custom visuals that I said I would do. Hopefully, next week…
Hi,
Did you ever post the sample file? I’ve tried to find the post you mentioned.
Hi, where can I find the sample power BI file please?
Do the 2 tables Walkstart andPVM have to be linked to the table where you have the values through relations?
They should not be linked
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?
If I were to add another time dimension, e.g. year, would the approach work?
Yup, it sure would
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.
Can this be applied to move three periods? E.g. i/o LY and Actual, can we show 2020, 2021 and 2022?
Not dynamically, unfortunately… you may want to check out the ultimate waterfall chart, as it is free and has a few more options. I also have a review of that chart here and on YouTube
Can you order the breakdown e.g. in the sequence of Mix, price, volume i/o ordering by their value?
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?
You are either not sorting by it in the chart or you have not ordered the column by sort order
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!
Thanks for the excellent tutorial! The legend now shows a new entry ‘Other’, would you know how to get rid of it?
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.
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
Make sure you have a custom sort order done of the buckets by the sort attribute, then order by it in the chart
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?
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.
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])
))
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!
Thank you sir! Glad it was helpful…
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.
I will be doing a follow up on this soon… Stay tuned…
Hello, is there any update about sorting by breakdown? I really need to sort by months by the breakdown is still sorting by values.
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?