Tips on setting up measures
This section discusses how to handle certain types of measures.
Percentage
Typically with percentages, the consolidation method should be average. Consider the case where the measure is 5% salary growth. If all the months of the first quarter had actual of 5%, you would want to represent the Quarter's actual and target as the average 5% and not the sum which would be 15%.
| Unit | Actual | Expected target |
|---|---|---|
| Quarter 1 | 5% | 5% |
| January | 5% | 5% |
| February | 5% | 5% |
| March | 5% | 5% |
Care needs to be taken with using percentages. Note that there are no weights associated with the consolidation. Consider the case where the measure is 10% increase in sales growth. One organization unit's actual numbers are 5% growth rate where the amount sold is 100,000 and another unit's growth rate is 15% where the amount sold is 1,000. The average of the percentages is 10% for the parent unit. But when you consider the sales amounts directly, the sales amount of 101,000 does not represent a 10% growth. In this case, it is best to use the original sales figures.
Ratio
Consider the case where a goal is a ratio, such as cost as a percentage of revenue. It would be best to use the original figures instead of a ratio as a measure. Chicago has a cost of 500 and revenue of 1000 which gives a ratio of 50% and New York with cost of 100,000 and revenue of 1,000,000 which gives a ratio of 10%. When you look at the parent, US East, a simple average of percentages (30%) does not reflect the real case which is 100,500 over 1,001,000. In this case, you need to use different measures involving the original numbers.
Yes / no
Yes/No type of measure is best used to track something that has to be done every period. For example, a survey is performed every quarter. As this is a non-accumulating type of measure, there is no consolidation over the period dimension, for example, the last value for the last quarter is used for the value for the Year.
If there are some quarters where the measure would not be done or you want to accumulate how many are done from one period to the next, it is better to set up the measure as an integer. Also, if you want to vary the scenario target as in the case where Expected target is to do something in April but the Best Case target is to do it in March, it is better to use an integer type of measure.
Consolidation method to use
When should you use sum versus average for the consolidation method for a Yes/No measure? Note as Yes/No is not accumulating, in the period dimension, the value of last child is used for parent (Q4 value is used for Year value.) So this discussion only deals with the unit dimension.
The performance value is the same for both sum and average but the appearance of the check boxes used for actual and target differ. Using average consolidation method limits the number of grayed check boxes and gives a user a better indicator.
For both sum and average, the actual and target values for an ancestor are based on the actual and targets of all the leaf plan objects (ones with no children) for that ancestor. So at the Corporate level all of the plan objects without any children are used in the calculation.
For purposes of the calculation consider checked to be 1 (Yes) and non-checked to be 0 (No). A grayed check box is used for other values. Only Marketing and North America, South America, Europe, and Asia have measures set.
Consider the consolidation method for the measure is average. Here Sales actual value is 2/4=0.5 and target is 4/4=1. The formula is Performance = 1 - | ((Actual - Target) / Target)|. Thus the activity value is 50%. Corporate actual is 3/5 and target of 5/5=1 yielding an activity value of 60%.
If the consolidation method was sum, the actual and target values differ but the performance value does not. For Corporate, the actual value is 3 and the target is 5. The display would have a grayed check box for the expected target for Corporate and Sales as the values are neither 0 nor 1.