KNOWLEDGE BASE

Creating Waterfall Chart Using Multiple Calculated Measures


Published: 20 Oct 2020
Last Modified Date: 19 Jan 2021

Question

I want to create a waterfall chart from multiple measures but unable to pivot the data source because some of them include table calculations. How can I create a waterfall char from multiple calculated measures?

Environment

  • Tableau Desktop

Answer

The attached CSV file, WaterFall_SampleData.csv contains only three measures: Net Sales, Cost of Sales and SGA In the following steps, Gross Profit and Operating Income are calculated in Tableau Desktop to create a warterfall chart with using these measures.
  1. Open WaterFall_SampleData.csv by Tableau Desktop.
  2. Click Analysis > Create Calculated Field. Create a calculated field names as Gross Profit including the following formula .
    [Net Sales]-[Cost of Sales]
  3. Create a calculated field named as Operating Income including the following formula.
    [Net Sales]-[Cost of Sales]-[SGA]
  4.  Create a calculated field named as 1. Fix Fields including the following formula.
    This calculated field is used to populate the horizontal frame of the waterfall chart.
    CASE [Segment]
    WHEN "Consumer" THEN TODAY() 
    WHEN "Cooperate" THEN TODAY()+1
    WHEN "Home Office" THEN TODAY()+4
    END
  5. Create a calculated field named as 2. Order Of Field including the following formula.
    This calculated field is used to decide the order of measures.
    CASE INDEX()
    WHEN 1 THEN 'Net Sales'
    WHEN 2 THEN 'Cost of Sales'
    WHEN 3 THEN 'Gross Profit'
    WHEN 4 THEN 'SGA'
    WHEN 5 THEN 'Operating Income'
    END
  6. Create a calculated field named as 3. Set Gannt Starting Point including the following formula.
    CASE INDEX()
    Case Index()
        When 1 Then Total(Sum([Net Sales]))
        When 2 Then Total(Sum([Cost of Sales]))
        When 3 Then Total(Sum([Cost of Sales]))
        When 4 Then Total(Sum([Cost of Sales]))
        When 5 Then Total(Sum([Net Sales]))
    End
    
  7. Create a calculated field named as 4. Water Fall Width including the following formula.
    IF INDEX() = 1 Then -[3. Set Gannt Starting Point]
    ELSEIF  INDEX() = 2 then -[3. Set Gannt Starting Point]
    ELSEIF  INDEX() = 3 Then TOTAL(SUM([Gross Profit]))
    ELSEIF  INDEX() = 4 Then TOTAL(SUM([SGA]))
    ELSEIF  INDEX() = 5 Then -TOTAL(SUM([Operating Income]))
    END
    
  8. Create a calculated field named as 5. Labels including the following formula.
    IF INDEX() = 1 THEN TOTAL(SUM([Net Sales]))
    ELSEIF  INDEX() = 2 then TOTAL(SUM([Cost of Sales]))
    ELSEIF  INDEX() = 3 Then TOTAL(SUM([Gross Profit]))
    ELSEIF  INDEX() = 4 Then TOTAL(SUM([SGA]))
    ELSEIF  INDEX() = 5 Then TOTAL(SUM([Operating Income]))
    END
    
  9. Place 1. Fix Fields with right-clicking and select 1. Fix Fields(Discreate), then click OK.
  10. Right click 1. Fix Fields in columns, then select Show Missing Values.
  11. Place 2. Order Of Field to columns.
  12. Right click 2. Order Of Field in columns, then right click the field > Compute Using > 1. Fix Fields 
  13. Place 3. Set Gannt Starting Point to rows, then then right click the field > Compute Using > 1. Fix Fields.
  14. Change the mark type to Gannt Bar.
  15. Place 4. Water Fall Width to size, then then right click the field > Compute Using > 1. Fix Fields.
  16. Place 5. Labels to label, then then right click the field > Compute Using > 1. Fix Fields.
  17. Right click 1. Fix Fields in columns, then uncheck Show Header.
Did this article resolve the issue?