Forum Discussion
Waterfall Chart using multiple measures
Hi,
Yes, you can do it via a workaround in DAX formula and a control table.
1. Create a table with matching "measure" name:
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()
)![]("C:\Users\YongC\OneDrive - BDF Group\Documents\Data\Support\PowerBI\PowerBI_20190312.PNG")
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):
Cheers!
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]
Anonymous 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?
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.
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':Measure Gen. Cash Flow Adjustment Order Waterfall Category Short Measure Title # & Short Title EBIT @ Budgeted fx 1 1: EBIT @ Budgeted fx EBIT 1-EBIT Restruc. Expense 2 2: Restruc. Expense Res Exp 2-Res Exp Currency Impact 3 3: Currency Impact FX 3-FX D&A (Now at OPBDA) 4 4: D&A (Now at OPBDA) D&A 4-D&A Cash Generated from Change in Restruc. Accrual 5 5: Cash Generated from Change in Restruc. Accrual Res. Acc. 5-Res. Acc. Cash Generated from Change in A/P 6 6: Cash Generated from Change in A/P A/P 6-A/P Cash Generated from Change in Inventory 7 7: Cash Generated from Change in Inventory Inv. 7-Inv. Cash Generated from Change in A/R 8 8: Cash Generated from Change in A/R A/R 8-A/R Cash used for Capex 9 9: Cash used for Capex Capex 9-Capex Total Generated Cash Flow 10 Total Generated Cash Flow TOT Total 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?
