Waterfall Charts Using Measures in Power BI

Power BI waterfall charts provide a great way for users to visualise how pieces of an overall plan (or results) are combined to contribute to an outcome. For example, you could use a waterfall chart to show how sales have increased by year and category as illustrated below using the standard waterfall chart in Power BI.

One of the features of the waterfall chart about is that “Year” and “Category” are both columns in the data model. The columns are added to the X Axis and the results “build” from left to right.

Measures, not Columns?

But what if you want to build a waterfall chart that “builds” on a concept such as the components of revenue e.g. Cost, Profit, Tax and Sales? See example below.  If you have these components as columns in your data model (eg as with General Ledger data), then you are fine.

But what if you don’t have such a data model, and each of the components has to be calculated as its own measure?  The standard waterfall chart does not support using measures on the x-axis, and hence you cannot create such a chart just using measures.

Disconnected Tables and Switch Measures

One solution to this problem is to turn the measures into columns in a table by using SWITCH measures and disconnected tables.  The steps to do this are as follows:

• Create a disconnected table containing the measure name you want to see on the waterfall chart axis
• write a SWITCH measure to display the measure values for each row in the table
• use the column in the disconnected table as a category in the Waterfall chart
• use the SWITCH measure as values in the Waterfall chart.

Disconnected Table with Measure Names

The first step is to create a table with measure names using Enter Data in Power BI Desktop. I like to add an ID column to use in the SWITCH measure as it is easier to write, plus you can use the ID column to sort the Waterfall chart.

After loading the table, sort the Measure Desc by ID column. I have called my table SwitchTable, but you could call it DriversOfRevenue or something similar if you wanted something more descriptive.

SWITCH Measure to Display the Values

Write the following switch measure to identify what measure values need to be displayed.

Display =
SWITCH (
    SELECTEDVALUE ( SwitchTable[ID] ),
    1, [Total Cost],
    2, [Total Profit],
    3, [Total Tax]
)

Create the Waterfall Chart

From here you can create the Waterfall chart.

• Place the column (mine is called Measure Desc) on the X Axis
• Place the measure onto values

I then did some tweaking to the chart such as changing the title, hiding the legend, etc.  Also, I couldn’t find a way to change the final bar of the chart – it simply is called “Total” with no seeming way to rename this (if you know of a way, please let me know in the comments below).  I added a text box over the top of this label so I could rename it.

Or You Could Use Calculation Groups

You could also use Calculation Groups instead of SWITCH Measures to solve this problem. You can read more about Calculation Groups here