I read about how to calculate CAGR here:
My dataset has sales volume by years. I want to calculate the compounded growth rate between the last five years. I wrote the following formula:
(lookup(sum([Off-trade Volume (mn litres)]),last())/lookup(sum([Off-trade Volume (mn litres)]),last()-5))^(1/total(count([Year])-1))-1
How can I test if this formula returns correctly or not?
Now, I would like to display for ONE company (using filters), for each geography that the company is in, what the latest yearly sales figure is, and what the CAGR has been for the last 5 years, both as texts. Then I want to repeat this, but with the CAGR as a color or size. However, I want to know: am I forced to display all of the years' sales volume in order for the calculated formula to work? Or can I just display the last year's sales figure and the 5-year CAGR will still display correctly?
Were you able to solve this issue? I am having a similar one. I want to create a CAGR for a category's profit. I want the size to show profit but color to show CAGR and am not able to figure out how to do so. Let me know if you hvae any best practices!