Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 :
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!!!
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.
Pi
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?
Pi
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
14 |
User | Count |
---|---|
44 | |
36 | |
24 | |
24 | |
22 |