Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
flavourabbit
Frequent Visitor

Waterfall Chart using multiple measures

Hello,

 

I know this must've been an old topic in Business Intelligence.

However, I didn't find any idea to realize what I'd like to achieve.

 

The thing is I'd like to create a Waterfall Chart with multiple measures.

Let's say that there's Fact Table containg accounting data (Revenues, Costs, Profits)

 

Thus it's a kind of running total, I can expect to draw the chart.

(Which item is main cause of profit decrease e.g., Ads/Logistics)

 

However, only solution I found at Google is restructuring the dataset like below

I don't think it's a good idea to manipulate the fact table, plus if I create another table, all the current measure refering the Table A would be not working with newly made one.

 

Is there any way to solve this?

 

Many thanks,

 

 

temp.PNG

29 REPLIES 29
cyongt_bdf
Advocate II
Advocate II

Hi,

 

Yes, you can do it via a workaround in DAX formula and a control table.

1. Create a table with matching "measure" name:

image.png

 

 

 

 

 

2. Create an additional measure for your waterfall chart, you may apply + or - for your measure to get the waterfall sentiment:

Waterfall value = 
    SWITCH (
        SELECTEDVALUE ( Table1[Index] );
        1; +[Measure 1];
        2; +[Measure 2];
        3; -[Measure 3];
        BLANK()
    )

3. Apply the new measure in your waterfall chart, with the category based on the table field created (Note that you need to sort it via the category, use hierarchical drill-down if needed):

 

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Cheers!

@cyongt_bdf Hi, I know this is really old. Do you have the sample pbix file? I am not following the. Create a table with matching "measure" name. Thank you 

This is amazing. Thank you!

Anonymous
Not applicable

Hi..Thanks for this solution. But how can you remove Total bar?? In my case, total bar is undesirable...moreover, can we change the colour of first and last bars to keep in lookalike?

this is my question!!

That's prety cool @cyongt_bdf, thanks

We dont need the column with the measures name in the table, just one with the names that we want in the axis, sorted by the index column. 
In the graph we only need that name column, and the graph sorted by that column. No need for drilldown

Any ideas on how to change he colors? Maybe with the breakdown field?

Thanks a lot for this. How can I make the color of starting bar and end bar same? basically i want to show 1st bar as begining value. then all components and then fianlly end value. I want to set these 2 bars as total and different color from increase/decreasepower bi ques.PNG

 
Anonymous
Not applicable

I have the same question

 

Thank you a lot for this amazing solution!

I have just tried it out and it works perfectly

Great solution cyongt_bdf,

One small improvement I figued out while trying this is to make an additional column in the new table. You will use this new column field as your Category. This field be whatever you want your X-axis titles to be. For example, "1: Measure 1", "2: Measure 2". This way you do not have to drill down to get the x-axis to look the way you want, and you can still make the order of the items static.

 

I used a DAX formula like the following to make this new column:
X-Axis Titles = 'Table1'[Index]&": "&'Table 1'[Measure]

 

 

@Stuart_S How does this make it static?

 

 For me based on this methodology, the columns in the waterfall shift based on whether the figures are accending or descrending. Can you share an example?

@Cali_2020 - My waterfall category table is at the bottom. This table is named 'Waterfall Table'.

 

I built a custom column in the 'Waterfall Table' I call 'Waterfall Category'. This measure is the first field in my waterfall vizualisation 'Category' section.

 

Waterfall Category = IF('Waterfall Table'[Measure]="Total Generated Cash Flow",'Waterfall Table'[Measure],'Waterfall Table'[Gen. Cash Flow Adjustment Order]&": "&'Waterfall Table'[Measure])
 
I built a measure I call 'Waterfall Value'. This measure is the first field in my waterfall vizualisation 'Y Axis' section.
 
Waterfall Value =
Switch(
Selectedvalue('Waterfall Table'[Gen. Cash Flow Adjustment Order]),
1, +[EBIT @ Budgeted fx],
2, +[Cash used for Restructuring],
3, +[fx Impact],
4, +[Depreciation/Amortization],
5, +[Cash Generated from Change in Restruc. Accrual],
6, +[Cash Generated from Change in A/P],
7, +[Cash Generated from Change in Inventory],
8, +[Cash Generated from Change in A/R],
9, +[Cash used for Capex],
10, +[Total Gen. Cash Flow],
Blank()
)
 
'Waterfall Table':
MeasureGen. Cash Flow Adjustment OrderWaterfall CategoryShort Measure Title# & Short Title
EBIT @ Budgeted fx11: EBIT @ Budgeted fxEBIT1-EBIT
Restruc. Expense22: Restruc. ExpenseRes Exp2-Res Exp
Currency Impact33: Currency ImpactFX3-FX
D&A (Now at OPBDA)44: D&A (Now at OPBDA)D&A4-D&A
Cash Generated from Change in Restruc. Accrual55: Cash Generated from Change in Restruc. AccrualRes. Acc.5-Res. Acc.
Cash Generated from Change in A/P66: Cash Generated from Change in A/PA/P6-A/P
Cash Generated from Change in Inventory77: Cash Generated from Change in InventoryInv.7-Inv.
Cash Generated from Change in A/R88: Cash Generated from Change in A/RA/R8-A/R
Cash used for Capex99: Cash used for CapexCapex9-Capex
Total Generated Cash Flow10Total Generated Cash FlowTOTTotal

 

Hope that helps.

hi
regarding the measure such as [fx_impact] where did you calculate them?
in order tables at the model? the Waterfall Table is just for collecting and arranging the data in order to create the waterfall?

it doesnt change based on values if you create an index. Once you assign order of the index, the chart always dispalys in that order. worked for me.

Hello,

 

I have seen that the "total" column can "disappear" by using a breakdown field; however, with this solution, how would you create that breakdown field?

 

Thanks!

How could we remove total bar in the end of this chart?


@gsaneja01 wrote:

How could we remove total bar in the end of this chart?


Have you been able to remove the "total" bar from the chart?

Really good idea and perfect instructions!

Anonymous
Not applicable

Are you able to confirm that the idea posted above worked succesfully? I haven't had time to revisit this, but if it worked for you I will mark it as the solution. 

Olivier1
Regular Visitor

You can actually create a new table containing the names of your measures (You can write them manually). You can then use this table as your category in your waterfall and create a new measure for your Y axis. My measure looks like ; WF Measures = VAR Meas = SELECTEDVALUE('Waterfall 2'[Column1]) return SWITCH(TRUE(); Meas = "Ending Inventory (LY)" ;1 ;Meas = "Beginning Inventory (Feb)";-1 ; Meas = "Production" ; 1; Meas = "Sales" ; -1;Meas = "Interco & Adjustments *" ;4 ;Meas = "Ending Inventory (Act)" ; 1; -10) **You can replace the numbers with the measure you want it to calculate**

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.