Waterfall Charts Using Measures in Power BI ๐Ÿ“Š - Excelerator BI

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

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

  1. Hey Matt,

    I have a project to analyze MoM Customer Growth with categories for โ€œStarting Customersโ€, โ€œCustomers Churnedโ€, and โ€œCustomers Gainedโ€. I have to present this data on a waterfall chart in power bi. My x axis is very crowded because it is concatenating the Months and the categories. The waterfall chart does not give me this option. Is there any way I can use Dax to remove the concatenation?

  2. Hi Matt, Thanks for this article.
    However, I have a question, how can I show one of my category (measure) as result column that starts with 0 on axis?

  3. Matt, can you provide a reference of how calculation tables can be used in a waterfall chart. I have a calculation table inside a matrix format, but unable to transition to a waterfall chart. Thank you

    1. Sorry, I don’t really understand what you are asking. This article shows how to create a table and use that in a waterfall chart. That is what you seem to be asking, which is what the article describes. As long as you have a column in your table to use on the x axis of your chart, it will work

  4. Hi, I was needing to show the variance of LY vs CY by using a Waterfall Chart. Also I was needing the total of Ly and CY as you are having in the first picture. It worked with your function but I can’t sort the buckets alphabetically (they can only be sorted with their correspondent values). Did you encountered the same issue?

  5. Hi, Thank you for the post, I am not sure I am following Disconnected Table with Measure Names. Can you please explain it to me? Tahnk you so much

    1. Hi Danzi,
      A disconnected table is a table in your model that doesn’t have a relationship with any other table in the model.
      The disconnected table is required so you can use switch to control the measures being returned to the chart.

  6. Can we use waterfall chart without measure. Because I ready calculate data in table . if we use data do a measure for waterfall the value is not correct.

  7. Hey, thank you for your help!
    I would like to get some help with power bi. i need to show period over period cycle, so Iโ€™m using the waterfall chart and use the โ€˜Typeโ€™ as breakdown and the visual makes the difference between the periods by itself. But, what if i want to make a calculate measure on top of this difference?
    For example, case when last_period_sale > 0 and diff< 1000 then โ€˜New_Basicโ€™ end as โ€˜Typeโ€™. Thank you ๐Ÿ™๐Ÿป

    1. At the heart, a calculation group is just like a disconnected table like the one shown in this demo. So create the calculation group instead of the disconnected table, and it should work. There is a link on how to create a calculation group above.

  8. Hi, I have a problem. First my date data is redundant in my dataset. I have different sets of breakdowns (both positive and negative numbers) for different categories in my dataset. I have tried to use the Dax examples to solve this problem, it doesn’t work. It replicates the breakdown for all the fields in all the category fields just like your first pictogram. can u pls help? thanks.

  9. 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

    1. Hi Chris, pls have u tried writing DAX for a waterfall chart that has different breakdowns (both + & – numbers) for different category fields in the same data set? Your formula only works if the breakdown is the same for all the category fields..

Leave a Comment

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

Scroll to Top