topic Re: "Hacking" a waterfall chart in DAX Commands and Tips

"Hacking" a waterfall chart

ItoDiaz — Fri, 17 Feb 2023 09:54:58 GMT

Hello all,

I have adapted a code that I found in the link below to do some waterfall charts :

Power BI & DAX Tutorial: Waterfall charts with beginning and end states in 5 minutes - YouTube

It works pretty well. The second part of the code generate a waterfall chart showing evolutions of places for trainings offered in the next four weeks.

I would need to change the way in which “Disponibles” (available) is shown. As we are talking about training places still available, it should be better to show it in its position but in green and positive.

I know this is not logical for a waterfall behavior, nevertheless, somebody would have an idea to do this?

The code :

%RempSes_PEB = VAR Tot_Proposees = CALCULATE(SUM('03_Sessions1'[Max]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK() || '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_AnnulSession = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]="X Nombre minimum de participants pas attei")) VAR Perdues_Sessmaintenues = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR Inscrits = CALCULATE(SUM('03_Sessions1'[Inscrits]), FILTER('03_Sessions1', '03_Sessions1'[Sup erreur ope]=BLANK())) VAR selectedBreakdown = SELECTEDVALUE('%RempSess_Breakdown PEB'[Bloque]) VAR SelectedCategory = SELECTEDVALUE('%RempSess_Categories PEB'[Categorie]) VAR SelectedTime = SELECTEDVALUE('03_Sessions1'[Type session]) RETURN SWITCH(SelectedTime, "Passé", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Perdues sessions maintenues", Perdues_Sessmaintenues, Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Perdues sessions maintenues", 0, Inscrits )), "Futur", SWITCH(SelectedCategory, "Proposées", SWITCH(selectedBreakdown, "Perdues Annul session", Perdues_AnnulSession, "Disponibles", [%RempSes_PdispoF], Tot_Proposees ), "Inscrits", SWITCH(selectedBreakdown, "Perdues Annul session", 0, "Disponibles", 0, Inscrits ) ))

The measure used

%RempSes_PdispoF = CALCULATE(SUM('03_Sessions1'[Dispo]), FILTER('03_Sessions1', '03_Sessions1'[Témoin suppression]<>"X"))

Some data if needed :

https://www.dropbox.com/scl/fi/phx9ydfuizuu0liwrveh3/Sessions-Waterfall-chart.xlsx?dl=0&rlkey=gr68u6p5i1p9i8udzgc31vh3g

Re: "Hacking" a waterfall chart

PiEye — Tue, 21 Feb 2023 14:16:47 GMT

Hi ItoDiaz

I've managed to get something to work, but this is a "proper" hack using stacked bar charts and made possible using the new recently released window functions as offfered by microsoft.

I've written a blog post on how to do it:

https://www.iheartdemography.com/post/hacking-a-waterfall-chart-in-powerbi

Essentially you use the window functions to work out some cumulative totals and then from there calculate the whitespace needed for some blocks of absolute value

Using these measures:

Waterfall Cumulative = calculate([Sum of Amount], WINDOW(1,ABS,0,REL, SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[Date]), ORDERBY(Sheet1[Date]) ))

Waterfall Cumulative -1 = calculate([Sum of Amount], WINDOW(1,ABS,-1,REL, SUMMARIZE(ALLSELECTED(Sheet1),Sheet1[Date]), ORDERBY(Sheet1[Date]) ))

Waterfall White Space = min([Waterfall Cumulative -1],[Waterfall Cumulative])

Waterfall Block -ve = If(AND([Sum of Amount]<0,DATEDIFF(DATE(2023,5,6), Max(Sheet1[Date]) ,DAY)<>0),Abs([Sum of Amount]))

Waterfall Block +ve = if([Sum of Amount]>0 ,[Sum of Amount])

Waterfall Block Special = If(DATEDIFF(DATE(2023,5,6), Max(Sheet1[Date]) ,DAY)=0,Abs([Sum of Amount]))

The blocks and whitespace can be loaded into a stacked bar chart which can "look like " a waterfall and offers more flexibility

Does this help as a starter?

Re: "Hacking" a waterfall chart

ItoDiaz — Wed, 22 Feb 2023 10:12:05 GMT

Hello PiEye

Thank you for your answer, I had lost hope of having one.

I had already thought about to do it using a bar chart as in the old excel methods. However, I thought that it could be possible to do something directly on the waterfall chart. I was very optimistic.

Your solution helps me because, it confirms that I should do it in this way and I hadn't thought about using ABS function.

Unfortunately, I couldn't try your solution because my company is always delayed to distribute Power Bi releases... I'm still working with October 2022 version and Window fonction is not available!!!

Re: "Hacking" a waterfall chart

PiEye — Fri, 24 Feb 2023 09:57:05 GMT

Hi ItoDiaz Great news - the windows functions were actually made available before the actual release, but just weren't officially documented!

See this blog for example (offset - one of the different window functions): https://www.purplefrogsystems.com/2022/10/previous-row-value-dax/

So please try them, they might still work but the only difference will be that the intellisense will not recognise it or suggest fields / errors etc.