Power BI & DAX Tutorial: Waterfall charts with beginning and end states in 5 minutes
Vložit
- čas přidán 14. 06. 2020
- Desktop file is available here: bit.ly/2BcVkD5
Link to the blog post: businessintelligist.com/?p=2637
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 tutorial 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.
You are a legend, this has saved us from purchasing a purchase third party visual. Thanks for sharing!
Thank you very much, my friend, I've searched about this topic and you helped me
Glad I could help!
Thank you for the video. You saved my day.
This is an absolute genius. Thanks for making my life easier :)
really really really really really thank you!!!
super video !
how do you alter the sort order? I have almost copied your set-up having a CategorySort and BreakdownSort column, however my waterfall will only order on value, not based on the CategorySort/BreakdownSort order
thanks for the video. but what if the number is negative?
Great video! Exactly what I needed! The only thing I still need to figure out is how to sort the variances, as someone else asked... Can't I use the breakdown ordem somehow?
yeah, i gotta find a good solution for that. I thought i had it but unfortunately it did not work right
How I can sort the values (volume,price,mix) in defined order
How many Breakdown are allow in above waterfall , I have to do 6 breakdown but while doing it in 6th breakdown it goes in other in graph area
Beginning date and end date I will select from slicers,and then the breakdown I wanted to see,can you please help
Why multiplying it by -1?
Simply genius, for a dafty like me it took ages to grasp this. Can you please share your thought process when you came up with this? Many thanks,
We have started a podcast series that we will do live every week or so, maybe that would be a good segment to talk about the background for how we come up with our videos and ideas
is there a way to, instead of creating the Breakdown one by one, use a Dim table? how would the SWITCH part look like?
I like your method and thank you for that. But I can't make it work if I have more than 2 categories :(
I want two totals to be two different colors
& I want the sorting order to be fixed by breakdown
is it possible ?
Can I sort that chart by breakdown?
This is great! I was trying to have a middle category bar (So: Start, Mid, End) but having some breakdowns between Start and Mid and different breakdowns between Mid and End. Is this possible you think? I can get the 3 category bars to show but then all breakdowns are showing in both gaps.
Having the same problem. I still haven't figured out a solution to this.
@@CinnamonBuns19 Since you also cannot sort the variances using this method, I switched to the Simple Waterfall custom visualization, where I can sort the variances as well as adding a middle category bar.
i think that Simple Waterfall chart is the way to go for anything that the built in chart cannot do
Is there a way to sort the total column first ? excel does that automatically but not power BI. Any advice would be appreciated
Thanks for the tutorial and files. I have a question regarding sorting - I need a certain order of the buckets which is fix and does not depend on the values of the buckets (ascending/descending). In your file/tutorial it's ordered largest to smallest. When I change e.g. Volume to 20 and Gross Revenue to 100, than these 2 buckets switch. How do I make the order of the buckets fix? Thanks
i don't think it is possible to do the way the chart works, it wants to show big increases first and end with the largest decreases to show the proper waterfall or at least proper from Microsoft's perspectives, so it always sorts on value unfortunately. I have some ideas on how to use a bar chart to make it look like a waterfall and have the sorting control option there but i still have a lot of work to do to make it work right
That's really weird that waterfall visual does not respect sort order, specified for the buckets column.
Hello my friend, Im having a really hard time to have this type information divided week by week using the built-in waterfall chart. So it would be a starting value, new added stuff (+ value), reassigned stuff (- value), Resolved stuff (- value) and the remaining that will be, for example, week end 06/27. The strange thing is that PowerBI is simply not "grabbing" my values as + or - even if my data source its correct. Can you help me?
How do you sort the variances? I did the same labels and it still sorts largest to smallest
i will do a quick video on this soon as I am getting a lot of questions about it, so please stay tuned
@@KnowledgeBankPro thank you kind sir!
I want this also!
Thank you for the explanation! Will be very useful. My only problem left is that I can´t sort the categories in the breakdown. I am using dates in my breakdown but they are getting out of order. Already tried sorting by column but with no sucess. If you have any idea how to solve this it would be lovely. Cheers
yes, this is a common issue... i am trying to find a good way to get it working right
@@KnowledgeBankPro Hi, have you solved this issue regarding the sort?
@@KnowledgeBankPro Any news?? your file saved my job
If you click on the 3 dots ... on top right corner of the chart, it gives you sort options (by *values*) of either category or breakdown, so the customised orders that we put in the category and breakdown tables don't apply. I think this is where it all falls down.
I was able to create a similar chart, but for some reason, I get an "Other" column that I cannot remove. I am showing all the categories, so this is not related to the max. breakdowns. Any suggestions?
did you solve it??
@@MrSebaceba yes, one of the categories was misspelled and that was causing the occurrence of the Other category. However, one thing that I’ve noticed is that the magnitude of each category is based on a comparison with all other categories except the first and last pillars. This aspect makes the overall graphic representation inaccurate. I was unable to figure out a way to include in the actual comparison the beginning and the end, so if you have any solutions on this, it would be highly appreciated.
Sorry, but the suggested technique did not work for me. The waterfall chart showed the value of "Volume Variance" as "Revenue Last Year" with a negative sign and the same value in the bucket "Volume Variance" with a positive sign; the other buckets and the current year total amount were zero on the chart. I replaced all calculated measures with hard-coded numbers to avoid any errors due to miscalculated measures, but the result was the same. Did Microsoft change how the waterfall chart works recently, which made this technique obsolete? I would appreciate some insight!
Okay, the issue was in a relationship that Power BI created automatically for me between the tables Category and Breakdown. Once I deleted that relationship, the chart started showing everything correctly! Thank you for sharing an awesome technique! 2 thumbs up!
@@vladsayapin8387 Thanks much much, saved the day with your solution, solved exactly the same problem i had :)
ty for sir!
Avoid the 'Sort' fields, they are useless since Power BI doesn't allow to sort graph categories by 'other column', it only has options to sort AZ or ZA by the column actually present in the graph; moreover, when Power BI finds those 'Sort' fields of integer type present in both tables, it will think both tables are related as @vladsayapin8387 points out, messing the whole graph. The video waterfall appears sorted just by change, because the breakdown values are in ZA order since the beginning: Volume, Mix, Gross, Discount. So, in order to have the expected waterfall graph, even with breakdown names sorted as needed you'll need 2 steps:
1. Avoid, as pointed, the 'sort' fields that appear in the video.
2. To ensure the proper order of Categories and Breakdown names use digits as prefixes, i.e. 01-Gross, 02-Volume, 03-Discount, etc. for Breakdown names; and 01-First Catg and 02-Second Catg for Category names.
@@vladsayapin8387 Thank you very much. I was seeking for the mistery for at least 2 hours till I decided to read the comments to find a related case. The idea to verify connections didn't cross my mind.
what is waterfall chart, what does it tells???always try to convey basics in a minute....
I dont understand why microsoft just make this possible without all these steps. I mean ZEBRA BI can do it so easily.
You are not wrong :)
This post sucks. Allthough it creates a waterfall with a begin and end value it doesn't support sorting based on specific sortorder. No matter how many people ask about it, you just don't reply to it. Even in your blogposts. Suggesting you will address it in a next video but eventually you never do. If you use months as breakdown, you always want those months sorted. If that's not possible this waterfall chart is simply useless.
Hola, sabes que intento crear un gráfico de cascada de la misma forma, pero al agregar un 4to ítem no me lo agrega de manera correcta, favor ayúdame a solucionar este problema, déjame tu email para compartirte mi problema