Calculation Measure
The Calculation Measure function provides flexible measure computation for multidimensional analysis, supporting various calculation types to enable complex analysis scenarios. This document details the types of calculation measures and their configuration methods.
- 1. Conditional Aggregation
- 2. Restricted Measure
- 3. Calculated Measure
- 4. Measure Control
- 5. Difference From
1. Conditional Aggregationโ
Conditional Aggregation supports summarizing measure data based on specified conditions and aggregation methods. Aggregation functions include:
| Aggregation Function | Description |
|---|---|
| Sum | Cumulative sum |
| Count | Count of selected dimension members |
| Min | Minimum measure value among dimension members |
| Max | Maximum measure value among dimension members |
| Average | Average measure value among dimension members |
| Standard Deviation | Sample standard deviation of measure |
| Population Standard Deviation | Population standard deviation of measure |
| Median | Median measure value among dimension members |
| Top Percent | Sum of top X% of dimension members by measure |
| Top Count | Sum of top N dimension members by measure |
| Top Sum | Sum until cumulative measure exceeds threshold |
- Using Conditional Aggregation: Aggregation functions compute under selected conditions (include or exclude).
Conditional Aggregation Functions and Examples
1. Sumโ
Description: Sums measure values for specified dimension members.
Example: Sum sales for "Beverages" product category:
Sum(
[Product].[Category].[Beverages].Children,
[Measures].[Sales Amount]
)
2. Countโ
Description: Counts dimension members meeting conditions.
Example: Count provinces with sales > 1M:
Count(
Filter(
[Region].[Province].Members,
[Measures].[Sales Amount] > 1000000
)
)
3. Minโ
Description: Returns minimum measure value among dimension members.
Example: Find lowest sales amount in product subcategories:
Min(
[Product].[Subcategory].Children,
[Measures].[Sales Amount]
)
4. Maxโ
Description: Returns maximum measure value among dimension members.
Example: Find highest sales amount across regions:
Max(
[Region].[Area].Children,
[Measures].[Sales Amount]
)
5. Averageโ
Description: Calculates average measure value among dimension members.
Example: Average order amount per customer group:
Avg(
[Customer].[Group].Children,
[Measures].[Order Amount]
)
6. Standard Deviationโ
Description: Calculates sample standard deviation (divided by n-1).
Example: Analyze sales volatility (sample standard deviation):
Stdev(
[Product].[Subcategory].Members,
[Measures].[Sales Amount]
)
7. Population Standard Deviationโ
Description: Calculates population standard deviation (divided by n).
Example: Population standard deviation of sales across countries:
StdevP(
[Geography].[Country].Members,
[Measures].[Sales Amount]
)
8. Medianโ
Description: Returns median measure value among dimension members.
Example: Median sales amount across product categories:
Median(
[Product].[Category].Members,
[Measures].[Sales Amount]
)
9. Top Percentโ
Description: Sums top X% of dimension members sorted by measure.
Example: Total sales of top 20% customers:
Sum(
TopPercent(
[Customer].[Customer Name].Members,
20,
[Measures].[Sales Amount]
),
[Measures].[Sales Amount]
)
10. Top Countโ
Description: Sums top N dimension members by measure.
Example: Sum sales of top 5 provinces:
Sum(
TopCount(
[Region].[Province].Members,
5,
[Measures].[Sales Amount]
),
[Measures].[Sales Amount]
)
11. Top Sumโ
Description: Sums members until cumulative measure exceeds threshold.
Example: Sum top customers until total sales exceed 5M:
Sum(
TopSum(
[Customer].[Customer Name].Members,
5000000,
[Measures].[Sales Amount]
),
[Measures].[Sales Amount]
)
Using Conditional Aggregationโ
Conditional aggregation computes under specified conditions (include or exclude).
โ Principle:โ
Conditional aggregation applies aggregation functions (e.g., Sum, Avg, Count) to dimension members meeting specific conditions, using Filter, Aggregate, or specific members (e.g., customer, product, region) to limit the data subset.
In MDX, conditions are embedded in the aggregation functionโs second parameter:
Sum(Dimension.Members, Aggregate(Condition, [Measures].Value))
โ Purpose:โ
- Finer Data Analysis: E.g., sum sales for โSpecialty Bike Shopโ customers only.
- Business Condition Modeling: E.g., calculate sales from VIP customers or specific regions.
- Flexible Reporting: Build complex measures via MDX without altering cube structure.
โ Example Comparison:โ
Without Conditional Aggregation:
Sum( [Product.Products].[Category].Members, [Measures].[Sales Amount] )๐ Unconditional sum of sales across all product categories.
With Conditional Aggregation:
Sum( [Product.Products].[Category].Members, Aggregate( [Reseller].[Specialty Bike Shop], [Measures].[Sales Amount] ) )๐ Sum sales for โSpecialty Bike Shopโ resellers, restricted to each product category.
2. Restricted Measureโ
Restricted Measure refers to measures under specific constraints, such as limiting variable ranges, relationships, or values. E.g., analyzing data points within a specific dimension range or with defined relationships, improving analysis precision and statistical significance.
- Select base measure field.
- Add one or more constraint dimensions and specify members or dynamic parameters.
- Optional: Enable Constant Selection to fix constraints against other query filters.
๐ก Note: Constraints can be dynamically set via parameters. See Parameter Usage in Restricted Measures.
Constant Selectionโ
When enabled, measure constraints remain fixed, unaffected by page filters or query conditions. When disabled, constraints adapt to query context changes.
Parameter Restrictionโ
Constraints support parameters, allowing users to interactively select dimension members, enhancing analysis flexibility.
๐ Note: Indicator is a special form of restricted measure with identical constraint mechanisms.
3. Calculated Measureโ
Calculated Measure supports custom expressions for complex computation logic.
Usage:โ
- Enter formula in the calculation measure editor.
- Reference existing measures, dimension members, or parameters.
- Use various mathematical and analytical functions.
Example:
AVG(LastPeriods([@Sales_MA_Windows], [Time].CurrentMember), [Measures].[profit])
Where:
[@Sales_MA_Windows]references a parameter.[Measures].[profit]references a measure field.
Toolbar Supportโ
- Dimension Members: Browse and insert hierarchy members.
- Calculated Members: Insert existing calculated measures.
- Parameter Management: Insert or create parameter variables.
See: Parameter Usage in Calculated Measures.
4. Measure Controlโ
Measure Control creates dynamically selectable measure fields, allowing users to choose measures during analysis.
Configuration Steps:โ
- Create a calculation measure, select Measure Control type.
- Define display behavior and add selectable measure fields.
- Configure input controls (e.g., measure selector widget) for user interaction.
๐ See: Input Control Widgets.
5. Difference Fromโ
Difference From supports difference or ratio calculations between dimension members, suitable for year-over-year, period-over-period, or version comparisons. Supports time and other dimension types.
Configuration Process:โ
- Select measure field for comparison (supports all measure types).
- Set base dimension (e.g., time).
- Configure comparison items:
- Current member.
- Previous N members.
- Next N members.
- Parallel members (specify level).
- Ancestor members (at specified level).
- Set comparison method:
- Difference:
A - B. - Percentage Difference:
(A - B) / Aor(A - B) / B. - Direct Ratio:
A / B. - Denominator Absolute Value: E.g.,
(A - B) / abs(B).
- Difference:
This flexible setup supports various comparison dimensions and methods, meeting common data comparison needs.
For more on parameter settings, input controls, and chart interactions, see: