Waterfall Charts Using Measures in Power BI - Excelerator BI

Waterfall Charts Using Measures in 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 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

8 thoughts on “Waterfall Charts Using Measures in Power BI”

  1. Chris Chitemerere

    Great post Matt. I did it differently to make the chart reveal real business insights. I structured it as follows:

    PAT Drivers Display =
    SWITCH(
    SELECTEDVALUE(PATDriver[SortOder]),
    1, [Revenue Actual],
    2, [CoS Actual],
    3, [Operating Expenses Actual],
    4, [D & A Actual],
    5, [Net Interest Expense/Income Actual],
    6, [Taxes Actual],
    7, [PAT Actual]
    )
    It is actually drivers of Profit After Tax (PAT). The Total bar chart is meaningless and formatted its color to white so that it does not display. if there a way of not showing Totals, i would have disabled it.

    Unfortunately, i cannot not attach an image of the chart

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top