Waterfall charts are used to show the running total as values where they are added or subtracted, in power bi we also have waterfall chart where the columns in the waterfall chart are color-coded for the view of increase or decrease in the values.
What is the Waterfall Chart in Power BI?
The waterfall chart is a kind of column chart that shows all the positive and negative variance values against the set targeted values. This chart helps us to understand how values are continued to contribute overall numbers over a period of time. This chart we would call “Positive & Negative Variance” values towards set goals. In this article, we will show you how to create a waterfall chart in Power BI. Read on.
To create Power BI Waterfall Chart you need data to work with, so you can download the excel workbook template from the below link which is used for this example.
How to Create a Waterfall Chart in Power BI?
In order to create the “waterfall” chart, we need to have actual values and targeted numbers over a period of time. Below is the monthly Target vs Actual numbers.
- You can use the above table to create a “Waterfall Chart” in power BI. Copy and paste the above table to Excel worksheet.
- Save the workbook in one of the folders. Now open Power BI and click on the “Get Data” option and choose the “Excel” as the data source option. Choose the file and upload and under the “Data” tab we should see this table.
- This isn’t the ready data yet to create a waterfall chart in Power BI, we need to find “Variance” between “Target” and “Actual” values. Under “Modelling” click on “New Column”
- This will ask you to name a new column.
- Give name as “Variance” and enter the DAX formula as “Actual – Target”.
- After the formula hits the enter key, it will create a new column for our existing table.
- Now using this data we can actually create a waterfall chart in Power BI. Before that lets how this chart looks like under the column chart.
- Come back to the “Report” tab and insert a blank column chart.
- Now drag and drop the “Fiscal Month” column to the “Axis” field and “Variance” column to the “Value” field to have the below chart.
- Ok, as you can see below all the negative values are downward bars and positive variance values are upward bars.
- By selecting the chart from visualization list click on the “waterfall” chart.
- Now automatically column chart will be converted to the “waterfall” chart.
- The problem here is this chart automatically sort the values based on the “Variance” column from highest to lowest, so that is the reason we have all our negative values (red bars) towards the end of the chart. To sort based on the “Month” number by selecting the chart click on three dots at the top right-hand side of the chart.
- This will open up below options for the chart.
- Go to “Sort by” and choose “Month”.
- Now our waterfall chart sorted based on “Month” and looks like this.
- So, if the variance value is positive against the targeted value then we have column bar color with “Green” color and if the variance value is negative against the targeted value then we have column bar color with “Red” color
- In the end we have one more bar saying “Total” this is the overall variance value against the target”.
Formatting of Waterfall Chart in Power BI
- By formatting this power BI waterfall chart we can make the chart look more beautiful as well. By selecting the chart click on the “Format” option.
- The first thing we need to format is to change the default color and font size of “X-Axis” values.
- Next, we will do the same for Y-Axis as well.
- Under “Data Labels” change color to “Black”, “Display Units” to “None”, and “Text Size” to 12.
- Under “Sentiment colors” we can change the bar colors of “Increase”, “Decrease”, and “Total” value columns.
- Now fill background color with “Pink” and make the “Transparency” as 20%.
- One final thing we need to add to this chart is “Title”. Under the “Title” formatting category does below changes.
- Ok, now we have a waterfall chart looks like the below one.
As you can see in the months “May, June, and July” target was not met so we have those column bars in “Red” colors.
Note: Power BI dashboard file can also be downloaded from the link below and the final output can be viewed.
Things to Remember
- It is a built-in chart in Power BI.
- We need to use the Variance column to Create the Waterfall Chart in Power BI.
- By default positive variance is shown in “Green” color, negative variance shown in “Red” color.
- Even though we don’t have a “Total” column i.e. Overall variance column Power BI automatically inserts this column.
Recommended Articles
Guide to Power BI Waterfall Chart. Here we discuss how to create and format a waterfall chart in Power BI desktop along with examples and downloadable templates. You can learn more about Power BI from the following articles –
