Hi Camille,

It’s tricky finding a solution without the full picture - but what I would expect would be to avoid using a SWITCH at all in your formula. It seems from your example that the Allocation Rule is a text-based property with a set of metrics (Allocation - A, Allocation - B…) but it really should be a dimension (e.g. Allocation Rule, with items A, B…) and a metric Allocation dimensioned by the Allocation Rule. That way if you need to add a new allocation rule, you have to add a new item to your dimension list Allocation Rule, and this will provide a new line in your Allocation metric.

The SWITCH function is great if you have specific calculations (e.g. x a different driver like headcount or sqm) in which case you’ll have to edit the formula each time you add a new rule anyway. In that case it’s still a good practice to add an Allocation Rule dimension list which will ensure you have thought of all cases.

Hi François,

Maybe I’m mistaking, but if the Allocation Rule is a dimension, I can’t have a specific formula for each ?

For example Allocation A is Turnover / Total Turnover and Allocation B is Cost / Total Costs

Thanks !

Yes, this is the tradeoff. If you want a specific formula for each rule, you’ll have to add this formula bit in the allocation formula.