Hi Ana planners,
Is there any way to keep the totals at 100% despite applying CAGR to set of percentages.
Image 1 shows the initial percentages and image 2 shows the new percentages after applying CAGR.
FY18 % are my base in image 1 on which CAGR is applied for an occasion called Party for subsequent years.
But the idea is to keep the totals to 100% despite the CAGR increases.
Can some one suggest a way to do it?
Go to Solution.
Because you are trying to get percentages to add to 100% that would suggest you are creating a mix% to total.
Here are some suggestions with an example of how to accomplish this.
First create a system module (See DISCO methodology) that contains your CAGR rates by Occasion.
See @DavidSmith and @HollyRieke excellent post on this.
You didn't provide these, but I think I'm pretty close.
Next you'll need to create a comparison to the compounded value to the total of all the compounded values. This will ensure your percentages add up to 100% as you can see below.
- Use a boolean to check for the first year since we don't want to compound the first year's base.
- The boolean should always check for the most likely scenario first, making your calculations more efficient.
- Next create a calculation on the percentages that uses the Occasion parent (use the SELECT function)
- Your final percentages add up to 100% and in this example it's pretty close to what you had in your second image.
- Note: In my example you could also put the base values in the system module instead of repeating them every year like I did.
Finally, here are the formulas to accomplish this.
The compounded value is a little long so I show it in the formula bar.
This really worked for me,