Forum Discussion
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
Sorry about that.
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
- lbendlin
Super User
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)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 🙂
- lbendlin
Sorry about that.
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) - lbendlin
No, that is not possible unless you want to do cross join acrobatics, or add fake 0 value transactions for the gaps.