topic PROBLEM: Waterfall chart with change value / Running Total in DAX Commands and Tips

PROBLEM: Waterfall chart with change value / Running Total

Anonymous — Tue, 09 Feb 2021 22:40:02 GMT

Hello, I am currently working on a waterfall chart. I only have the "change" values. So waterfall chart is not working. I tried to solve this with a running total which is not perfectly working, as it does not show me the running total for the "total" of the given categories. Does anyone have a solution to this?

This is the basic principle of the table:

Day	Change	Values
01.01.2020	Mix	100
01.04.2020	Mix	0
01.07.2020	Increase	90
01.07.2020	Mix	50
01.07.2020	Decrease	-20
01.12.2020	Mix	0
01.12.2020	Increase	0
01.12.2020	Decrease	0
01.01.2021	Increase	100
01.01.2021	Decrease	-100
01.05.2021	Decrease	-20

I used the following code:

Running Total = CALCULATE( SUM('Modell (2)'[Values]), FILTER( ALL('Modell (2)'), 'Modell (2)'[DAY] <= MAX('Modell (2)'[DAY]) && 'Modell (2)'[Change] = MAX('Modell (2)'[Change] ) ))

Now it gives me the running total by category (change), but it does not show me the sum of the running totals.

The data should add up in total and within the categories the values should also add up further if there is no value in a quarter.

Hope I could explain the issue

Thanks for the help

Jonas

Re: PROBLEM: Waterfall chart with change value / Running Total

lbendlin — Mon, 15 Feb 2021 02:31:33 GMT

Try this version instead:

Running Total = var d=max('Modell (2)'[DAY]) var c=max('Modell (2)'[Change]) var ov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[Change]=c && 'Modell (2)'[DAY]<=d),'Modell (2)'[Values]) var tov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[DAY]<=d),'Modell (2)'[Values]) return if(HASONEVALUE('Modell (2)'[Change]),ov,tov)

Re: PROBLEM: Waterfall chart with change value / Running Total

Anonymous — Mon, 15 Feb 2021 11:37:03 GMT

Hi lbendlin , thanks a lot for your help and the proposal, looks quite good already, only for the last quarter it shows me the wrong total. I assume it is because the negative value is the only value? How can I fix that? Best would be to have the running total in every line.

Thank you for your help 🙂

Re: PROBLEM: Waterfall chart with change value / Running Total

lbendlin — Mon, 15 Feb 2021 14:51:26 GMT

Sorry about that.

Re: PROBLEM: Waterfall chart with change value / Running Total

Anonymous — Mon, 15 Feb 2021 15:08:33 GMT

Total looks good now but is it also possible to have also the other values copied down? So that in Qrt 2 2021 we see -140,190,150
Then it looks perfect.
Thanks!!!!

Re: PROBLEM: Waterfall chart with change value / Running Total

lbendlin — Mon, 15 Feb 2021 15:11:03 GMT

No, that is not possible unless you want to do cross join acrobatics, or add fake 0 value transactions for the gaps.

Re: PROBLEM: Waterfall chart with change value / Running Total

Anonymous — Mon, 15 Feb 2021 15:13:05 GMT

Alright. Still awesome that helps already a lot!