KB8813: How to create a Percent to Total metric without OLAP Services permissions in Microstrategy Developer

Although users with permissions to use Strategy OLAP Services can more easily create "Percent to Total" metrics on the report grid, other users who don't use Strategy OLAP Services may also create a "Percent to Total" metric. To achieve this, the user must have three metrics.
 

• Create a regular metric with report level aggregation:
   
  Note: By default, this metric is calculated at the report level, i.e. the aggregation is matched to the lowest level attribute in the report. Typically, this type of metric is created by dragging and dropping a fact as shown below:
   
  

•  
  Using the above metric, consider the following example. This report contains the report level metric to calculate the revenue contribution of the each call center to the region:
   
  

• Create a level metric that aggregates at the desired level (i.e. the level of the total).
   
  In this metric example, the user needs to calculate the revenue at the region level. This metric will serve as the denominator for the percent to total metric. To create this metric, perform the following steps:
   
  
• Create a regular metric with report level aggregation as discussed earlier.
• Highlight the "Level" option.
• Find the Region attribute from the data explorer and drag and drop into the level definition list.
• Change the filter to "ignore" and grouping to "standard".
• Save and Close.

•  
  Users can also select any attribute for the grouping as long the attribute is not lower than the lowest level shown in the template.
   
  Next, bring the metric into the report. Notice the "Revenue_by_Region" metric aggregates the total number of each region:
   
  

• Create a compound metric based on the previous two metrics, "Revenue" (Call Center Level) and "Revenue_by_Region" (Region Level).
   
  This compound metric will be a "Percent to Regional Total" and created as follows:
   
  

•  
  Bring the "Percent to Regional Total" metric into the report. Make sure to change the format to "%".
   
  

Note: If the "Revenue_by_Region" is not required on the template, removing it will still generate correct results on the report:
 

 
For more example of advanced metrics, refer to the following Strategy Knowledge Base documents:
 

• KB4000-7X0-0359 - How to create a metric that counts non-buying customers in the Strategy 7.x Product Suite
• KB4000-7X0-0312 - How to create a report with percent-to-total information for different bands using Custom Groups in Strategy Agent 7.0

For information on Strategy OLAP Services and the "Percent to Total" functionality, refer to the following Strategy Knowledge Base document:
 

• KB4000-721-0914 - Derived Metric functionality is not available in Strategy Desktop 7i - 7.2.1