I have a mix of products which must always total 100%. When forecasting, I would like the ability for the user to either a) type in the product % mix, or b) increase one whilst simultaneously decreasing another (of their choosing) via a dashboard. If possible, could anyone help with how to set up point b?
@JenniferMontgomery Certainly possible! The setup will of course depend on the specifics of your requirements.
If it is a one to one relationship, you can create a line item when the user inputs the "offset product" and then use the SUM function to aggregate the offset amounts. You would then create a forecast line item that takes into account the Product Mix Inputs as well as the increase + offset amounts. Below is a simple example of how you might set this up.
Here is a way you can do it based on holding certain values constant and overriding others. In this example the East region is held constant, the South region is overridden, and the Central and West regions are allocated the remaining amount of the total initial target based on a percentage of their values to the total region. Therefore, your initial target total for USA is the same as the total for the region target. I'm assuming you could recreate this same functionality utilizing percentages instead of dollars.
You could also try and utilize the breakback functionality but that doesn't work for percentages.
I prefer the solution provided by @smithevan18It is also a commonly used approach in Anaplan. If you download the FP&A for SaaS model, you will see the implementation of this method and you can study it and replicate it in your model