Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)
No, that is not possible unless you want to do cross join acrobatics, or add fake 0 value transactions for the gaps.
Alright. Still awesome that helps already a lot!
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)
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!!!!
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |