Compounded Annual Growth Rate (CAGR) in MicroStrategy

The idea behind using a compounded annual growth rate is to smooth out the rate of change that something sees period over period for a successive number of periods.  Out of the box in MicroStrategy there is no function that creates this value automatically, so if a CAGR calculation is needed, either a custom metric or a custom subtotal is required.

The calculation for a compounded annual growth rate looks like this:

formula

In the CAGR equation we divide the ending value by the beginning value, raise it by a power — in this case the number of years that compounded — and subtract the 1 from it to get a percentage value.

…and in Excel, the formula to do this will use the caret to denote that we are raising the proportion of change by a power equivalent to the number of periods that the data spans.

CAGRinExcel

To do this in MicroStrategy, the best place to apply this logic is in a subtotal.  For the uninitiated, the ability to create new subtotals is very powerful, and often the best way to shoehorn a report look and feel into a grid.

To create a new subtotal for CAGR in MicroStrategy we have to specify the exponent using the power() function, and the At sign is going to be the

subtotal

Here’s the code for the calculation:

1
(POWER((LAST(x) {@}  / FIRST(x) {@} ), (1 / COUNT(YEAR) {@} )) - 1)

The second to last step in the process is to add the subtotal to a report.  Since this subtotal is for a year, adding the CAGR means that it should be the last column on the grid report.  To make the CAGR subtotal visible as an option, it needs to be added to the list of available subtotals for the metric.  In this example we are working with a revenue calculation so we need to move the new subtotal as being available to the revenue metric.

addsubtotal

The final piece once there is a report that contains both the Year attribute and the Revenue calculation is to add the subtotal at the right level.  For this, go to the advanced subtotals section of the grid, and put the CAGR subtotal across the Year level.

acrosslevel

The final output should look something like this:

report

Note that the calculation used in this subtotal is for year, but the concept can apply to any time period.  With the same implementation, but using a different attribute (month) the calculation will only differ by the attribute name:

1
(Power((Last(x) {@}  / First(x) {@} ), (1 / Count(Month) {@} )) - 1)

 

4 Replies to “Compounded Annual Growth Rate (CAGR) in MicroStrategy”

  1. Hi Christopher,

    ((Last(x) {@} / First(x) {@} ), (1 / Count(Year) {@} ))

    The “Last(x)” and “First(x)” in your formulas, are those directional words, if typed into the formula it will identify my last and first years and go from there, or is are those for the reader and the reader of the blog has to type in their own identifier?

    Also, I’m new to the {@} symbol is this something I type in to the formula during creation or is it something completely different? Please explain, if you would.

    1. I used a custom total to accomplish this — the @ symbol implies that the calculation will occur at the level of the report.

      The first() and last() portions are simply functions in MicroStrategy. Those will work as-is for you. For this calculation it was a way to pull the beginning and end values…and this is all native to MicroStrategy. If your attribute name is not “year” then that is what will have to change.

      The trick here is that you have to create your own custom total. If you get that, your are well on your way.

      ~ Chael

  2. Thanks for sharing – this is great. How does one format the custom CAGR Subtotal? Because we are using a Sales $ metric the values display as $..

Leave a Reply

Your email address will not be published. Required fields are marked *

*