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

ParastooHi Matt,

Thanks a lot. It helped me in a big way.

Sharlee CrewsHey 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?

Maaz AkramHi 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?

Daryl EllisMatt, 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

Matt AllingtonSorry, 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

joseHi, 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?

Danzi DPHi, 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

Jason CockingtonHi 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.

DineshKattaHii Matt,

Thanks for your way forward. It helped me yo sort my problem.

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

LeandroAmazing solution! Thank you!

StavHey, 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 ๐๐ป

Sai Sarayu VadaHi Matt,

Could you please explain how to add a waterfall chart using Calculation Groups?

Matt AllingtonAt 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.

AHSAN UL HAQChris Many Many thanks for this forum

Please advice me to hide Total Column from waterfall chart.

Thank you

PhuongSo clear. Thanks.

Bob GrahamI love this and see how it should work. But, mine produces no numbers. It looks like it not even calculating. I proved the measures work and are correct.

Here is my call for help on the community.Powerbi.com.

https://community.powerbi.com/t5/Desktop/Waterfall-using-Multiple-Measures-not-producing-anything-source/td-p/1531785

DDHi, 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.

Matt AllingtonIt’s difficult to provide support here. I suggest you ask a question at community.powerbi.com and provide as much detail as you can

Uche UcheYou are a great expert in Excel. Thanks

Thanks

Chris ChitemerereGreat 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

MattThanks for sharing Chris

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

PavelMatt, Chris, thanks for you ideas! Didnt yiu find a way to disable “Total” field?

Uriel IlanSELECTEDVALUE

Anther new DAX function that is not supported in Power Pivot

Sad…

Matt AllingtonAgreed. And I donโt see it changing actually.

DanWould it be possible to use someting else instead of Selectedvalue in this case?

MattYes, you can use MAX() instead

DanThanks for the input Matt!