Power BI custom Waterfall with multiple measures
In this post I would like to show you how to create a waterfall chart with multiple measures grouped by category using standard visualization methods of Power BI. Please consider that is not a perfect solution the purpose of that is to give you some Ideas how such challenge could be solved in Power BI!
Before we can start the implementation, the following must be taken into account, namely that this implementation must meet certain conditions:
- The category to be grouped by needs a fixed order. There are methods to achieve this with a dynamic order (rank), but this is not considered in this article.
- The order must be a numeric field. The order is only used for the calculation, for the display name in the chart the category itself can be used.
Step 1 — Data preparation:
Ensures that the category contains a sequence attribute for determining the order. In our case, it is the field “Cat_Sort”. Our actual category field is the field “Category”, which will be used later in the waterfall chart.
Step 2 — Basis of Visualization:
First, let’s create a standard “Stacked Columns Chart” and drag the “Category” field to the “Axis” area and our two measures “Value A” and “Value B” to the “Values” area. Let’s assign an appropriate name and display the labels of the Data Bars. Basically, it should now look like this.
Step 3 — Measure Preparation:
In order to create a waterfall chart that builds on each other by category, we need two additional measures.
(1) Can EARLIER be used in DAX measures? | Javier Guillén (wordpress.com)
Step 4 — Creating the Waterfall Chart:
Now that we have the two measures we need, we can start converting our “Stacked Column Chart” into a Waterfall chart.
First, we need to drag the new calculated measure “Value prev Category” into the “Value” area of the chart. In doing so, the order of the key figures must be respected. The key figure “Value prev Category” must be placed at the top in the first position.
Our chart diagram should look like this now.
To make our chart look like a waterfall chart, we need to adjust the “Data Color” of the newly added key figure “Value prev Category” according to the background of the chart, in our case white.
Furthermore we have to hide the “Data Label” for our key figure. This should be done in the “Data Label” area. To achieve this, the “Customize series” property must be switched “on” and the “Show” property must be set to “off” for the “Value prev Category” key figure.
The final result should then look like this.
Link to the Power BI File: https://www.dropbox.com/sh/flq6el83adi3x6x/AAAFX7AKWGTydetUNzVmvTYKa?dl=0
