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: 

 

Now I need to build the same chart in Power BI. I tried many times, but I could not achieve the required result.

The main requirements for this Waterfall chart are:

- Budget NPAT must be on the far left.

- Actual NPAT must be on the far right.

The variance between budget and actual profit should be shown through breakdown items such as:

Gross Margin

Variable Cost

Fixed Cost

and others

Positive values should appear in green, and negative values should appear in red, exactly like the Excel Waterfall chart.

My question is:
How can I create this type of Waterfall chart in Power BI with the same layout and structure as the attached example?

If possible, I would prefer a practical example using a Power BI file. I will share sample data to use for the implementation.
Also, please let me know if the data needs a specific structure or format to work correctly with a Waterfall chart in Power BI.

categoryvalue
Budget NPAT4,261,765
Gross Margin2,654,696
Variable Costs-285,573
3rd Party Cost Recovery-435,897
Other Income32,571
Fixed Costs1,324,187
Support Cost738,545
Depreciation-664,t354
Bad Debt Provision799,987
Finance Exp & Inc-1,537,534
Income Tax-637,856
Actual NPAT6,444,542
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.

     

     

6 Replies

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    Super User
    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!

     

    • 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.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    6 months ago

    Hi manah77g 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    6 months ago

    Hi manah77g 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you