Best Practice : Associate a metric with a dimension ?

  • 17 April 2022
  • 3 replies

Badge +2

For example if I have several metrics corresponding each to an allocation rule, and I want to define the allocation rule to be used for each entity of a company.

Is there a best way than using a SWITCH statement to associate a metric with a dimension.

In my example : SWITCH(Entities.Allocation Rule, “A”, ‘Allocation - A’, “B”, ‘Allocation - B’, ...)

It is not totally optimal because I have to update the formulas each time an allocation rule is added.

Thanks in advance !


Best answer by francois 19 April 2022, 08:58

View original

3 replies

Userlevel 4
Badge +3

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.

Badge +2

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 !

Userlevel 4
Badge +3

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.