I’ve created a Commissions Forecast metric to calculate the difference between YTD Commissions and Total Target Commissions. The value is then spread over the remaining months left in the current year.
I’d like to maintain the values from this metric and have it to flow through to my Total Compensation metric which has more dimensions defined in the metric structure. Any tips on how to accomplish this?
To align your formula, you can use the formula playground from your total compensation metric and play with the formula and modifiers.
To allocate your source metric Commissions Forecast to your target metric total Compensation, you will need to add the missing dimensions.
In this case: ID, Employment Type, Employment Status, Location, State.
Pigment automatically does it as you can see on the formula playground above. It takes the results and allocate by constant a result on all missing dimensions, so your source metric will be copied over the total cardinality of your missing dimensions.
So when you just add the source metric to a target that has more dimensions, in reality Pigment does an implicit allocation:
Another logic would be to split the results accross your missing dimension so the aggregated total is exactly the same as your source metric, using the modifier ADD split:
On the last line of the table you can see that allocating using add split reflect the results of your source metric.
To know more about add and modifiers:
Hope it helps!
Best,
Benoit
Thanks for your response, Benoit! That makes sense, however I received an error message indicating a long run time: “Formula timed out (> 3 minutes)”. After working with the support team, we decided to move forward with the following formula below:
'Commissions Forecast'
Instead of using the Department dimension, we defined our modifier using the ID dimension given the other dimensions (i.e. Department, Employment Type, Employment Status, State, Location) were properties of the ID dimension. This resolved the timeout message and returned the target data.
