Power BI & DAX: How to Make Waterfall Charts Work (showing starting and ending values of the bridge)
Vložit
- čas přidán 11. 06. 2020
- 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.
Link to blog: businessintelligist.com/?p=2632
An elegant fix to one of the most frustrating issues with default Power BI waterfall charts. Given how popular Waterfall charts are in finance I was shocked at how lacking Power BI's default one is. Thanks so much!
yeah, i agree, you would think this should be available intuitively out of the box
I was confused at the beginning, but I managed to figure it out after watching this video multiple times. Thanks for sharing your knowledge!
you are welcome!
Clever approach. Thank you!
Amazing , been looking for this for a long while
glad it was helpful!
Great job! I have been working on a similar project, and your video was incredibly helpful. Thank you for sharing your knowledge.
I have a few comments:
- If someone is experiencing issues with the order of the initial and last bars (Categories), you may check if the category sorting is correct. Additionally, you may sort the category column based on the category sort with "Sort By Column" of "Column Tools".
- It's essential that the names of the categories precisely match the names used within the SWITCH statement; otherwise, the graphic won't appear.
amazing, just what i was looking for.
Glad I could help!
Great, you solved my issue... Thanks a lot!
Thanks a lot making a very precise video. Thanks a lot. It's rally helpfull.
Thank you that was very helpful 👍👍👍
Can't give enough upvotes. The most informative PBI video I have found. Thank you thank you thank you
Glad it was helpful!
@@KnowledgeBankPro I'm a PBI novice. However I was able to adapt your code for one period that I have. However now I'm trying to adapt it to multiperiod/year data. The problem is that the period and year depends on a selection but if it's odd then I output first part of the waterfall and second part if the index is even. Is this possible or do you think this can't be applied to multiperiod data?
I don't think IS EVEN() ISODD() works within the context of Switch selected value.
hi, sorry, it's hard for me to answer, i don't think i quite understand what you are trying to do
@@KnowledgeBankPro it is tricky heh. I have say fiscal period 1-13 by multiple years. If I select 1 period, the adaptation of your approach is flawless. However if I have 2 periods selected, the model gets confused and displays data out of order because next periods data has the same index as the previous, so the data isn't displayed in order I want it to. So what i thought of doing is adapting the index by which it's sorted to display data correctly. I haven't been able to figure it out yet and put it aside for now
@@saidaharle3637 I am planning to do a follow up video, I will try to get address some of the questions posted either here or on my blog in the video
Thank you Sir, very useful video
Thanks For this Video..Very helpful..I have followed the steps mentioned in this video however I was hoping if there any way we can fix the breakdown field and not have it fluctuate based on the amounts?
Thanks for sharing this
You are very welcome, thank you for watching!
This video is amazing! Thanks a lot for sharing. I do have one question, I hope you can help me. I need to not only sort the Initial and Final values (LY and Actual) but also sort the breakdown values but I haven't been able to. I don't want to have the breakdowns changing order. Is there a way to sort both Category and Breakdown and keep them static?
This is a great video. A question about this is at 9:53. When you hover over the variance in the waterfall, it should be able to tell you the rate of increase/decrease compared to the previous level. However, using this approach it appears the change is always 100% as can be seen in 9:53. Please do you know of a way to obtain the waterfall chart that can show the correct percentage increase/decrease compared to the previous bars?
What about the case i am using the x axis values as dynamic? I cannot hard code the values by using new table.....Thanks
Hello , Thanks for the Amazing solution.
I followed the same,but the problem is I have nine breakdown categories including start and end will count to 11.Shown this by the Approach shared by you.
Bu could not able to link the last breakdown category and ending value
Is there a way to show multiple bucket categories over time like MoM? I have to show MoM customer retention (starting, gained, churned, ending) in a waterfall chart in power bi. It is easily done in Excel but having trouble with it in power bi.
This is really great. Is there a way to add an intermediate category with different breakdowns than what you have here (like maybe one additional breakdown just in that intermediate category before calculating the total at the end?)
Unfortunately not, you will need to take a look at the custom visuals. I was generally underwhelmed with most them however...
is there a way to, instead of creating "PVM[PVM]" one by one, use a Dim table? how would the SWITCH part look like?
how did your put same color for last year and total
Is it possible to
customize my column without auto format
How to apply a custom order to the breakdown?
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 like in your video and not according to the order of the defined categories.
i don't think you will be able to with the default chart... you may want to try the Ultimate Waterfall (free) chart... i think it works better, you can watch my video on details here: czcams.com/video/JdPitPh57ZE/video.html
@@KnowledgeBankPro Yes I did but I don't see when we use the BreakdownSort. The elements of the breakdown are not in the desired order.
@@nicolasl946 did you try clicking on Breakdown column then going to Column Tools->Sort by Column and selecting Breakdownsort as a column to sort by?
Hi, thanks for posting. Is there some way to create a waterfall that could change the start total of the bridge based on a slicer? So for your example, maybe you have a drop down to select a start point of last year, forecast, or plan with each case still ending at actual. Thanks, hopefully that question is clear.
with some DAX wizardry it should be possible, but unfortunately, it would require some mad DAX skillz
@@KnowledgeBankPro I tried this one, I know is not perfect but maybe all together can achieve it:
Cascada Chart =
VAR Categoria =
SELECTEDVALUE(Aux_Cascada[Cardinal])
VAR Comparativo =
SWITCH(TRUE(),
SELECTEDVALUE(Aux_Segmentador_Cascada[Cardinal]) = 1,
CALCULATE ([Todas las Medidas], FACT_Flash_Resultados[Escenario] = "Actual", SAMEPERIODLASTYEAR(Calendario[Fecha])),
SELECTEDVALUE(Aux_Segmentador_Cascada[Cardinal]) = 2,
CALCULATE([Todas las Medidas], FACT_Flash_Resultados[Escenario] = "Presupuesto"),
CALCULATE([Todas las Medidas], FACT_Flash_Resultados[Escenario] = "4+8")
)
VAR Actual = CALCULATE(
[Todas las Medidas], FACT_Flash_Resultados[Escenario] = "Actual")
VAR Resultado =
SWITCH(TRUE(),
Categoria = 1, Comparativo,
Categoria = 2, Actual,
Actual-Comparativo)
RETURN
Resultado
There are some Spanish words cause I´m from Mexico but, basically:
Categoria is for Category in the waterfall Chart, the first and the last column over there.
Comparativo is for the start value, I used last year and some budgets.
Actual is for the last bar, the current measure value.
Lastly in Resultado, the dax function iterates over the waterfall X Axis so, if it has a category value (1 or 2) function assign those values, for all the other values in the middle, the breakdowns, assigns the difference between Actual and Comparativo. All selectedvalue were used to catch a value from a Slicer.
Amazing! I was wondering if it’s possible to hide blank breakdowns, mainly cause when I use slicers some categories has no values, becomes zero. I tried using hasnovalue and isblank functions but the main problem is that the category (not the breakdowns) turn blank… any ideas? Thanks!!!
normally if a measure returns blank(), then the data point is not displayed.... but it's hard to give you a good answer without understanding exactly what the data looks like
@@KnowledgeBankPro I completely understand, I have to provide the context. It seems that is a modeling problem ¿could be possible? I think this is the real problem because I´m (sadly) working with an unique table which contains fact and dimension indistinctively. Thanks for answering me. I solved the problem changing the breakdown field with a column from the table, the one that contained the categories, instead of the categories from the breakdown table created from that. In that way it becomes dynamic and blank() data remains occult.
@@jugarGV awesome, glad you were able to get past it.
How do you sort the breakdown? In your example, it's all automatically sorted by the value (descending), but what if you want to sort by specific order like alphabet?
you can use sort by column feature to specify the sort order and then use that in the visual
@@KnowledgeBankPro Thanks to you i was able to create a waterfall chart where my revenue fluctuation by category can be compared , and i put my category as break down column but even I already set up custom sorting to my category column by another column , my waterfall chart doesn't sort in that order .
I’m also facing the same issue on sorting. Were you able to find a solution?
HI Andre, it works well for me only when the total revenue Last Year is greater than one in Current Year. When the total revenue is greater this year, the order of the waterfall chart changes and instead of having Last Year as the first category, I have the Current Year as first, and the increase and decrease in price and volume are also incorrectly depicted in this case. Not sure where I am erring.
Me too!!! So frustrating! Have you found a solution?
Click In the visual > More options > Sort By > "Select the relative option". Solved the fixed the starting column.
Hello, thank you for the video I found out very useful. I have 4 KPI'S which is my measures and I created the waterfall chart by using my 4 KPI'S and the waterfall chart looks good but some of the waterfall values are not matching with my KPI'S values when i tried to run it and i don't know how to fix this issue. I was hoping is there any solutions for this issue? please and thank you.
I encountered the same and realised one of the categories in my DAX formula was not 100% the same as how I named my Categories/Breakdown.
Impressive job! would it be possible to do this to compare several years? Let's say 2017-Bridge steps-2018-Bridge steps-2019-Bridge steps-2020
Anything is possible with enough DAX tinkering, but you may want to take a look at the Simple Waterfall chart custom visual. It is free and makes this job much easier... I have a review of that visual on the channel as well.
@@KnowledgeBankPro Thank you
@@KnowledgeBankPro if this was to be done in Dax, which formula would you suggest ? I’ve tried wrapping the switch with an if function based on the period criteria but that doesn’t seem to work.
I’m trying to build basically the same chart. Could you maybe show how your database in excel is organized?
I created a tutorial that you might want to take a look at here: czcams.com/video/BfACOEiPK8M/video.html also, look for my video on Simple waterfall chart visual, I think you might find it easier to work with
KnowledgeBank Thank you! Actually I found this video on the weekend and also read your blog. After that I was able to create the chart. Thank you very much!
@@bperalis that's awesome! glad it worked
Thanks for helping us with this method. Having an issue with the breakdown sequence, even though I sorted with an order column, still getting sequence based on the value. Any info regarding this would be appreciated. Thank you
make sure you are picking the right sort option in the visual... also, make sure you use custom sort order to make sure categories are sorted accordingly
@@KnowledgeBankPro hi this method not quite working for me so badly
@@KnowledgeBankPro Is there a way that the breakdown can be shown in a custom order rather than value order? Want the elements to appear in the same order as shown on income statement, not by magnitude
@@tonyspano4306 yes, you can chose the sort order on the chart and pick category, make sure that category is sorted by sort order column
@@KnowledgeBankPro thanks very much for your reply and most importantly the video itself. I was referring to the breakdown and not the category. I'm hoping to be able to select the order in which the individual bars of the breakdown appear and not by magnitude.
How can I make the buckets stay in the same order regardless of their value?
unfortunately, the basic waterfall chart does not support it. I am working on a different way to do this that hopefully will work
Would this work in direct query mode? Since you cannot create tables in direct query mode...
i would have to check, i think it should, i don't think there is anything in this logic that would stop it it from working in DQ
Thanks. Could you please provide a sample power BI file? I'd like to see the input table structure. Thanks
i have a tutorial for this in this video with the link to the blog and files in the description: czcams.com/video/BfACOEiPK8M/video.html
And how to handle if breakdown elements are above 11?
to my knowledge, it should work with any amount of breakdown elements
Any ideas how to block zero values from visual?
replace zeros with blanks?
Don't take this the wrong way but the VPM effect formulas you use aren't the most "correct" ones, consider using the Harvard method which is in my opinion makes more sense.
how you have hide the total?
don't remember anymore, sorry :)
Hello,
I have another solution to achieve same result
It is a matter of proper data preparation
Measure End Point Value
NL LY 10
NL ACT 15
VOL LY 20
VOL ACT 15
go on in such pattern for next buckets
then select End Point into Category, Measure into Breakdown and Value into Value
it works in my case:)
rgds
Maciek
looks interesting, I will have to try it myself
I’ll try your solution. But can you explain a bit more what your matrix above is? Is it a table you create? How can you have 2 measures with the same name? (NL and NL, VOL and VOL)? Thanks
This is avery usufull information for sales GAP analisys.
Ty sir!