Waterfall Chart Help

[manah77g]

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.

category	value
Budget NPAT	4,261,765
Gross Margin	2,654,696
Variable Costs	-285,573
3rd Party Cost Recovery	-435,897
Other Income	32,571
Fixed Costs	1,324,187
Support Cost	738,545
Depreciation	-664,t354
Bad Debt Provision	799,987
Finance Exp & Inc	-1,537,534
Income Tax	-637,856
Actual NPAT	6,444,542

[Hans-Georg_Puls · Accepted solution]

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!

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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.

[Hans-Georg_Puls · Accepted solution]

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]

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.

[Shreyash_Sanket]

Use simple waterfall chart from custom visual this will meet your requirements
1.You can define the pillar you need 

2.for all the break down it will show the red/Green depending on the +- Volume 

drwback : You cannot color code dynamically to the bars

[Hans-Georg_Puls · Accepted solution]

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.