allocation to keep totals to 100%

akprasa1

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?

 

 

 

image1_55012.PNG

Image_2_55012.png

JaredDolich

@akprasa1 ,

Great question. 

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.