SYMPTOM:
Compound metrics with smart totaling enabled do not work if the component metrics use different functions for subtotals, such as the combination of Sum and Avg. Consider the following example:
Revenue = Sum(Revenue) { ~+ }
Total Subtotal Function: Sum
Units Sold = Sum () { ~+ }
Total Subtotal Function: Average
Revenue / Units = Revenue /
Total Subtotal Function: Sum
The subtotal displayed for Revenue/Units is 41.33, which is the sum of the category-level revenue values divided by the sum of the category-level units sold. The desired subtotal is 35,023,708 / 211,868 = 165.31.
CAUSE:
A smart subtotal calculated over a compound metric uses the total subtotal function defined in the compound metric to calculate the component metrics. Since Revenue/Units is configured to use Sum, the Sum function is applied to both Revenue and Units Sold when calculating the subtotal for the compound metric.
ACTION:
In Strategy 9.x-10.x, an option, "Default," may be found in the menu for the total subtotal function. If this option is chosen in the compound metric, the subtotal for the compound metric will revert to the functions chosen in the component metrics and the expected result will be generated.
