Forum Discussion

manah77g's avatar
manah77g
Regular Visitor
6 months ago
Solved

Waterfall Chart Help

Dear Experts, I work in a fuel sales company. I was asked to create a Waterfall chart similar to the one attached in the image. The current chart was created using Excel. The attached:    N...
Need Help
  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    6 months ago

    Hi manah77g ,

    I failed to meet all of your requirements. Coloring the first column wasn't possible with the standard waterfall chart visual. But maybe you're still satisfied with my solution. I did the following:

    1. Entering your table using "Enter data"
    2. Switch to Power Query
      1. Removed the t from row 8
      2. Added an index column (a very important step to keep the category order)
      3. Changed type of "value" column to decimal number using context menu "using local..." and an appropriate region for your number format
      4. Removed your last row (the Power BI waterfall calculates its own total)
    3. Closed Power Query
      1. Set Sortby Index of category column to index (a very important step to keep the category order)
      2. Set format of value to Decimal number and adapt the details like the number of decimal places
      3. Add a waterfall visual
      4. Add "category" to Category
      5. Add "value" to value.
      6. Check an appropriate summarization setting for value (Min, Max, Sum should work)
      7. Adjust the setting of the waterfall chart:
        1. Columns->Colors: Adjust color of total column
      8. Add a textbox with "Actual NPAT" to cover the "Total" string

     

     

    Find attached a pbix Demo!

     

    Hope that helps and meets at least the most of your requirements!

     

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    6 months ago

    Dear manah77g,

    I'm glad if I could help.

    It might be interesting for you to have a look at a custom waterfall visual:

    1. https://marketplace.microsoft.com/en-us/product/power-bi-visuals/WA200000195?src=office&tab=Overview
      1. It's free
      2. First and last column may have a different color but not dynamically
    2. Or a more sophistcated one: https://inforiver.com/analytics-plus/waterfall-chart/
      1. Not free
      2. You can adjust almost everything

    But I have no experience with one of these custom visuals.

     

     

majid154a's avatar
majid154a
Helper II
6 months ago

Dear Hans-Georg_Puls ,
note that manah77g is my account

Great job on the solution, especially regarding the removal of the eighth row. Thank you very much for your valuable help.

I still have two challenges:

First: How can I set the Budget color to be different from green and red? Since it does not represent an increase or decrease, but rather a fixed and predefined budget.

Second: How can I make the Total (Actual) dynamic so that it appears in green when the value is positive and in red when it is negative? Because I tried another data, the total became negative but it appears in green color.
Is there another type of Waterfall chart that can support this requirement?

I truly appreciate your cooperation and support in helping me solve this challenge.

Hans-Georg_Puls's avatar
Hans-Georg_Puls
Super User
6 months ago

Dear manah77g,

I'm glad if I could help.

It might be interesting for you to have a look at a custom waterfall visual:

  1. https://marketplace.microsoft.com/en-us/product/power-bi-visuals/WA200000195?src=office&tab=Overview
    1. It's free
    2. First and last column may have a different color but not dynamically
  2. Or a more sophistcated one: https://inforiver.com/analytics-plus/waterfall-chart/
    1. Not free
    2. You can adjust almost everything

But I have no experience with one of these custom visuals.