Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help with simple waterfall chart

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,

Need Help
Show and Tell
Tutorial Requests

15 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        littlemojopuppy
        Community Champion
        5 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Here is a screenshot of what I have achieved so far

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ，

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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's avatar
    littlemojopuppy
    Community Champion
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      5 years ago

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

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