KB11205: A metric based on an average returns unexpected results in MicroStrategy depending on the level at which it is calculated.

SYMPTOM:
 
The following steps relate to a scenario in which a user is reporting on the values of a particular metric aggregated at the level of a parent attribute and its child attributes on different reports. For illustrative purposes, using the Strategy Tutorial Project, the parent attribute selected is Customer Region; the child attributes are Customer Region and Customer City.
 

• A user creates a metric called "avg units sold" which is defined as the average (arithmetic mean) of the fact Units Sold. The formatting of the metric values is set to two decimal places.
• The user then creates a report with Customer Region in the rows and the newly created metric avg units sold, and no filter. When executed as a grid report, it returns the following results:
  

•  
  Note the value in the first row of 40.91 for Customer Region = Northeast.
   
  
• The user then drills down on the Customer Region = Northeast row, having selected the Keep Parent While Drilling option in the Report Data Options menu, to the child attribute Customer State. The resulting child report contains seven rows of data with the values of "avg units sold" for the individual elements of Customer State. For illustrative purposes, Grand Total is activated giving a total of 282.85 over the seven rows:
  

•  
  It might be expected that this grand total, divided by the number of rows, must be equal to the average returned for the relevant row in the parent report, in other words 40.91. However, 282.85 divided by 7 is in fact 40.40.
   
  
• If the user returns to the parent report and drills down again from Northeast, this time to Customer City, the child report has 63 rows and a grand total of 2682.78. The equivalent calculation, 2682.78 divided by 63, results in 42.58:
  

• Finally, if the user drills from the first row (Customer State = Connecticut) to Customer City, the grand total is 653.26 giving an average over 15 rows of 43.55:
  

Intuitively, this behavior is not expected. The user might expect the average aggregated at the level of each parent attribute to be equal to the sum of the values as aggregated on the child attribute, divided by the number of elements in the child attribute. So, for example, one might expect the value of "avg units sold" for Customer Region Northeast to be the same as the average of the values of that metric for the Customer Cities belonging to that region.
 
CAUSE:
 
The reason that different values are returned is that the underlying calculation of the average is different at each level of aggregation. This may be illustrated with the following simplified hypothetical example. Consider the following report results which show an AVG(Base) metric aggregated against a parent and child attribute:

 
This might result from the following underlying values of the Base metric:

 
At the level of Parent = 20, the value for Avg(Base) is not (2+3+4)/3 = 3, as might intuitively be expected. It is in fact (3+1+2+4+4)/5 = 2.8.
 
ACTION:
 
Users receiving unexpected report results of this nature should therefore review the definition of the "average" metric and the levels at which it is aggregated.