cancel
Showing results for
Did you mean: Regular Visitor

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

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

1 ACCEPTED SOLUTION  Super User III

``````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(isfiltered('Modell (2)'[Change]),ov,tov)``````
6 REPLIES 6  Super User III

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

Alright. Still awesome that helps already a lot!  Super User III

``````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(isfiltered('Modell (2)'[Change]),ov,tov)`````` Regular Visitor

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!!!!  Super User III

``````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)`````` Regular Visitor

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 🙂  Announcements #### Welcome to the User Group Public Preview  