Help with simple waterfall chart

[shaunmag]

I'm having problems setting up a simple waterfall chart to show reduction in cost for a business over time. Any help would be much appreciated!

We have the following values in my table (each row relates to a property):

• Total Annual Cost (£)
• Cost Reduction Start Date (date)
• Cost Reduction Figure (£)

What I want to show is quite simply: For the Total Annual Cost to be shown on the left, then the Cost Reduction Figures as and when they hit across the chart (Cost Reduction Start Date), showing the resulting annual cost on the right (for which I have created a measure: Total Annual Cost - Sum of Cost Reduction Figure).

However... I can't get it right. In Excel it's super simple but in PowerBI it isn't, and feels quite limited? I'm obviously missing something. Happy to post the file if someone is will to take a look at it.

Thanks,

[littlemojopuppy]

You should get a message that I'm requesting permission to download

[littlemojopuppy]

Howdy!  So I found this article and am trying to implement it, with some success.

I'm able to get the starting and ending values to appear in the waterfall.  But the year to year changes aren't properly reflected.  What's even more odd is they are showing up correctly in a matrix, so I'm inclined to think there is some kind of oddity specific to waterfall charts. 

The code for the measure being used in the waterfall is as follows...

Bridge Values = 
    SWITCH(
        TRUE(),
        ISFILTERED('Calendar'[Year]),
        CALCULATE(
            [Total Cost Reductions],
            KEEPFILTERS('Calendar'[Year])
        ) * -1,
        ISFILTERED('Waterfall Walk'[WalkStart]),
        SWITCH(
            SELECTEDVALUE('Waterfall Walk'[WalkStart]),
            "Opening Balance",
            [Total Cost],
            "Ending Balance",
            [Targeted Annual Cost],
            BLANK()
        ),
        BLANK()
    )

At the moment I'm kind of hoping that @Anonymous or another moderator can offer an idea...but will still keep trying

[shaunmag]

Thank you so much, I really appreciate the effort! Hopefully we (the 'Royal' we) can somehow get it working

[littlemojopuppy]

Sure...I'd be happy to take a look.  Post a link to Google Drive or Dropbox and I'll grab it.

[shaunmag]

Thanks, I really appreciate that - please see below link with all sensitive data removed:

https://drive.google.com/file/d/1r0X0V51TVhOOaXN9LyEsOctKpFr7GRP7/view?usp=sharing

As I previously said, I just want to show what the total cost at the left of the chart (I've had to use a card to do the same), and what the resulting total cost is on the right once the total cost reduction has been aplied. Hopefully I'm just missing something reaally obvious?

[shaunmag]

Thanks - There's only one table I'm using, does that matter? 

For now I've simply put visual cards at either side of my waterfall chart to show the before and after values of the numeric value that is changing over time. It's not great but it'll do. I'll have a proper read of the tutorial you sent over, I'm sure there's a way to do that in PowerBI automaitcally, but oddly it doesn't give any option for me to do it in the list of possible fields in the visual

[Anonymous]

Hi @shaunmag ，

Whether your problem has been resolved? If no, please provide some sample data in your model(exclude sensitive data)  and the waterfall display in the excel in order to provide you a suitable solution? Thank you.

Best Regards

[shaunmag]

Hey - No I'm afraid not. If I post the file, might you able to take a look? There seems to be no obvious way for me to include the spend today, as well as the resultant spend. I can only show reduction over time and then a sum of that reduction at the end.

[littlemojopuppy]

Hi!  To get the waterfall to work, you want to have a measure that calculates period to period change in Cost Reduction.  That gets plugged in as the value, and then use year/month on the axis.

[shaunmag]

Here is a screenshot of what I have achieved so far

[shaunmag]

Thanks - so would that measure simply be:

Period Change = Total Annual Cost (£) - Cost Reduction Figure (£)

This would provide a measure that simply provides the new cost figure for each property.

[littlemojopuppy]

I guess?  Difficult to say without seeing some sample data or the results of what you're getting.

I find waterfalls in Excel to be horrible but in Power BI fairly easy as long as you have the measures defined correctly.

[shaunmag]

Thanks - I tried creating that measure and including it into 'value' but its just made it even worse. I'll check out the tutorial you linked to, thanks

[littlemojopuppy]

I re-read your original post.  Maybe the problem has to do with the timing of when cost reduction is realized???  If so, you want to look at your data model and specifically with relationships between your date table and the cost reduction date.

Then you could create a measure that's something like this

CALCULATE(
	SUM([Period Change]),
	USERELATIONSHIP('Calendar'[Date], 'TableName'[CostReductionDate])
)

[littlemojopuppy]

I assume you think the image you posted is not correct?

I think where you're going wrong (but I can't confirm this without having sample data) is that you should be looking for the change in cost from quarter to quarter, not (total annual cost - cost reduction)