Percentage variances

[manne]

HI guys, I have a problem where I cannot see a solution.

In my cube I have an element which is a margin calculation and I do this via a rule:

[Gross Profit %] = [Gross Profit] / [Total Revenue];

and this works as desired.

Now I also wanted to have the variances between ACTUAL and BUDGET, so I added in my SCENARIO/VERSION dimension a consolidated element [VAR ACT VS BUD] and added the elements Actual and Budget to it and set the weight of Budget to -1. So TM1 was given me the variance between actual and budget on all elements and it worked fine, except on the element [Gross Profit %] . There the rule calculated the margin of the variances, but I wanted to see the variance between the Actual margin of 20% and the Budget margin of 25%, so -5%.

So I added the following to my rule calculation:

[Gross Profit %] = IF( ATTRS('SCENARIO', !SCENARIO, 'RULE EXCEPTION') @= 'Y', STET, [Gross Profit] / [Total Revenue]);

so that the rule calculation will not be performed on my Variance element where I set the the attribute 'RULE EXCEPTION' to Y.

This new rule worked actually on the lowest element level just fine, but does not work on a consolidated level. There it is summing up the variances of all N elements and not building a variance of the consolidated value.

Any idea what I can do to get this working a consolidated level ?

Thanks so much..Manfred

[Duncan P]

The weighted variance consolidation will only work on leaf (N:) level cells. It will not calculate the variance of the consolidated level. For that you will need a rule.

Code: Select all

['VAR ACT VS BUD'] = N: STET; C: ['ACT'] - ['BUD'];
['Gross Profit %'] = ['Gross Profit'] \ ['Total Revenue'];

As the variance calculation is in front of the Gross Profit % calculation it will override it for the variance item on the scenarios dimension. You will not need the conditional in the gross margin calculation.
I have used \ instead of / as it gives 0 instead of an error where revenue is 0.

I would read the developer guide about the precedence of rules and the behaviour on overlap. It will make all this clear.

[manne]

Thanks - this done the trick. I have used the condition as I have a few variance consolidations and so thought to make it more general. But I guess I have to add a rule now for all these variances.

Any advise for a feeder on such a rule with N: STET and C: ?

Thanks again and a nice sunday !