its really useful, but i wanted to change the starting position name and the ending position name dynamically. could you please help me how can we do that?
thing is when i change the values the soting is always from top breakdown to bottom by value how to avoid that? like breakdown A=100 is always before (to the left) breakdown B=50 i would like them to have places on axis i choose
Thank you for this video, it helped me with my charts, but do you know how I can manually sort the x axis? I noticed that the declines are always on the right hand side and I want my x axis to be manually sorted
I dearly liked this method and everyone was amazed. The only challenge I have is that my breakdowns are not sorting. Does anyone know what could be the problem. The sort column data type is whole number
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
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
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!
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.
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?
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.
@@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 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?
@@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.
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
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.
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?
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
You are a legend, this has saved us from purchasing a purchase third party visual. Thanks for sharing!
This is an absolute genius. Thanks for making my life easier :)
its really useful, but i wanted to change the starting position name and the ending position name dynamically. could you please help me how can we do that?
thing is when i change the values the soting is always from top breakdown to bottom by value how to avoid that? like breakdown A=100 is always before (to the left) breakdown B=50 i would like them to have places on axis i choose
Thank you for this video, it helped me with my charts, but do you know how I can manually sort the x axis? I noticed that the declines are always on the right hand side and I want my x axis to be manually sorted
Why multiplying it by -1?
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!
how to amend the code when the difference is i.e. Diff = YTD - SPLY (same period last yr)? this code doesn't work in this case any idea?
Thank you very much, my friend, I've searched about this topic and you helped me
Glad I could help!
Did your breakdowns sort in the order you wanted them to be? If so, how did you do it?
I dearly liked this method and everyone was amazed.
The only challenge I have is that my breakdowns are not sorting. Does anyone know what could be the problem. The sort column data type is whole number
is there a way to, instead of creating the Breakdown one by one, use a Dim table? how would the SWITCH part look like?
Beginning date and end date I will select from slicers,and then the breakdown I wanted to see,can you please help
I want two totals to be two different colors
& I want the sorting order to be fixed by breakdown
is it possible ?
Is there a way to sort the total column first ? excel does that automatically but not power BI. Any advice would be appreciated
How I can sort the values (volume,price,mix) in defined order
Can I sort that chart by breakdown?
thanks for the video. but what if the number is negative?
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
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.
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
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.
Well done for finding the solution and thank you for sharing it. Could you please explain why it is so tricky and how did you find that out?
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
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
I like your method and thank you for that. But I can't make it work if I have more than 2 categories :(
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.
really really really really really thank you!!!
Thank you for the video. You saved my day.
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.
what is waterfall chart, what does it tells???always try to convey basics in a minute....
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?
super video !
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