What Are Level Metrics and there examples?
Level metrics in MicroStrategy are metrics that allow you to define the level (attribute) at which the metric is calculated, independent of the report structure. They are primarily used to override the default behavior of metrics that calculate based on attributes included in the report.
Key Components of Level Metrics:
1. Target: Defines the attribute level for calculation (e.g., Category, Region).
2. Filtering: Determines how report filters affect the metric calculation (Standard, Absolute, Ignore).
3. Grouping: Specifies whether data is grouped (Standard) or not (None).
Interview-Friendly Example
Dataset
Category Subcategory Revenue
Electronics Phones 100
Electronics Laptops 200
Furniture Chairs 150
Furniture Tables 250
Scenario 1: Revenue at the Category Level Including Displayed Subcategories
• Target: Category
• Filtering: Standard
• Grouping: Standard
Explanation:
The metric calculates revenue grouped by category but respects the report filter, which shows only the subcategories displayed on the report.
Report Filter: Subcategory = Phones, Laptops
Result:
Category Revenue
Electronics 300
How to Explain in an Interview:
• “In this scenario, the metric respects the report filter, so only Phones and Laptops are considered. The calculation is grouped at the Category level, resulting in Electronics revenue being 300.”
Scenario 2: Total Revenue for All Subcategories in Displayed Categories
• Target: Category
• Filtering: Absolute
• Grouping: Standard
Explanation:
The metric calculates revenue at the category level but considers all subcategories within the displayed categories, ignoring the subcategory filter.
Report Filter: Subcategory = Phones
Result:
Category Revenue
Electronics 300
How to Explain in an Interview:
• “Here, the metric ignores the subcategory filter but still respects the category filter. It calculates revenue for all subcategories under Electronics (Phones and Laptops), giving a total of 300.”
Scenario 3: Total Revenue for All Subcategories in the Project
• Target: None (Entire Project)
• Filtering: Ignore
• Grouping: None
Explanation:
The metric calculates total revenue across the entire dataset, ignoring all report filters.
Report Filter: Subcategory = Phones
Result:
Metric Name Revenue
Total Revenue 700
How to Explain in an Interview:
• “In this case, the metric ignores all filters and calculates the revenue across the entire dataset. This ensures the total revenue is always 700, regardless of the report filter.”
Scenario 4: Revenue at Subcategory Level
• Target: Subcategory
• Filtering: Standard
• Grouping: Standard
Explanation:
The metric calculates revenue for each subcategory shown on the report, respecting the filter.
Report Filter: Subcategory = Phones, Laptops
Result:
Subcategory Revenue
Phones 100
Laptops 200
How to Explain in an Interview:
• “This metric respects the report filter and calculates revenue for each subcategory displayed in the report. Phones revenue is 100, and Laptops revenue is 200.”
Key Points to Highlight in the Interview
1. Concept Clarity: Clearly define target, filtering, and grouping.
2. Real-World Application: Show how level metrics solve problems by overriding report-level attributes.
3. Examples: Provide at least two practical examples like the ones above, emphasizing different filtering and grouping behaviors.
4. Flexibility: Explain how level metrics make analysis flexible by calculating metrics at levels independent of the report’s attributes.