cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jonasabb
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.

Screenshot 2021-02-09 233609.jpg

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

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)

View solution in original post

6 REPLIES 6
lbendlin
Super User III
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.

Alright. Still awesome that helps already a lot! 

lbendlin
Super User III
Super User III

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)

View solution in original post

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!!!!

lbendlin
Super User III
Super User III

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.

jonasabb_0-1613388825897.png

Thank you for your help 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors