Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
ItoDiaz
Helper I
Helper I

"Hacking" a waterfall chart

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?

 

ItoDiaz_0-1676627385556.png

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=gr68u6...

3 REPLIES 3
ItoDiaz
Helper I
Helper I

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

PiEye
Resolver II
Resolver II

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors