Forum Discussion

flavourabbit's avatar
flavourabbit
Frequent Visitor
8 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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]

 

 

Cali_2020's avatar
Cali_2020
Helper I
6 years ago

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?

  • rtneupane's avatar
    rtneupane
    Frequent Visitor
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • ron_l100's avatar
      ron_l100
      Regular Visitor
      4 years ago

      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?