Creating Waterfall Chart Using Multiple Calculated Measures

Answer

Follow along with the attached packaged workbook to the right of this text in the "Attachments" section.

In the attached CSV file, WaterFall_SampleData.csv, there are 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 waterfall chart using these measures.

Open WaterFall_SampleData.csv in Tableau Desktop.
Click Analysis > Create Calculated Field. Create a calculated field with the name Gross Profit with the following formula:
```
[Net Sales]-[Cost of Sales]
```
Create a calculated field with the name Operating Income and use the following formula:
```
[Net Sales]-[Cost of Sales]-[SGA]
```
Create a calculated field and name it Fix Fields to include the formula shown below. 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
```

Create a calculated field named Order Of Field with the formula listed below. 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

Create a calculated field named Set Gantt Starting Point to include the following formula:

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

Create a calculated field named Water Fall Width to include the following formula.

IF INDEX() = 1 THEN -[Set Gantt Starting Point]
ELSEIF  INDEX() = 2 THEN -[Set Gantt 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

Create a calculated field named Labels with 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

Place Fix Fields to Columns by right-clicking and select Fix Fields(Discrete), then click OK.
Right-click Fix Fields on Columns, then select Show Missing Values.
Place Order Of Field to Columns.
Right-click Order Of Field on Columns, then right-click the field and select Compute Using > Fix Fields.
Place Set Gantt Starting Point to Rows, then right-click the field and select Compute Using > Fix Fields.
Change the mark type to Gantt Bar.
Place Water Fall Width to Size, then then right-click the field and select Compute Using > Fix Fields.
Place Labels to Label, then then right-click the field and select Compute Using > Fix Fields.
Right-click Fix Fields on Columns, then un-check Show Header.