KNOWLEDGE BASE

Calculating a Compounded Annual Growth Rate


Published: 07 Mar 2015
Last Modified Date: 21 Nov 2016

Question

How to calculate a compound annual growth rate.

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the workbook attached below.

Step 1 - Create a Parameter

  1. Click the drop-down arrow to the right of Dimensions on the Data pane and select Create Parameter.
    1. Name the parameter "N Years"
    2. In Data Type > select Integer
    3. In Current Value > input 
    4. Leave the Display format and Allowable values as they are. 
  2. Click OK.

Step 2 - Create a Calculated Field

  1. Select Analysis > Create Calculated Field > name it "CAGR".
  2. Enter in the formula below:
    POWER(ZN(SUM([Sales]))/LOOKUP(ZN(SUM([Sales])),-[N Years]), 
    ZN(1/[N Years])) - 1
    
  3. Click OK

Step 3 - Build the view

  1. Drag "Order Date" to Columns and drill down to "Quarter"
  2. Drag "Sales" to Rows
  3. Drag "CAGR" to Rows 
    1. Right-click on "CAGR" and select Dual Axis
    2. Right-click on "CAGR" and select Edit Table Calculation > Choose Specific Dimension > Choose Year of Order Date only
  4. From the Sales Card, drag Sales to Details and select a chart as desired (in this example, the Bar chart is selected)
  5. From the CAGR Card, drag CAGR to Details and select a chart as desired (in this example, the Line chart is selected)
  6. Right-click the measure in the CAGR Card and select Edit Table Calculation > Choose Specific Dimension > Choose Year of Order Date
  7. Drag Sales to ToolTip if desired

Step 4 (optional) - View the data in a crosstab 

  1. Righ-click the sheet and select Duplicate as Crosstab 
  2. Swap Rows and Columns as desired
  3. Move “Year of Order Date” to the right side
To view these steps in action, see the video below: 

Additional Information

Compound annual growth rate is calculated using the following formula, where N is the number of years:
((End Value/Beginning Value)^(1/n)) – 1
Value at current date / Value at beginning date
To the power of (1/number of periods between the two dates)
Minus 1 

Note: You can also confirm that your CAGR calculation is correct by using this third party website: http://www.investopedia.com/calculator/cagr.aspx
Did this article resolve the issue?