If you have not yet introduced yourself to the basic concepts of flex.bi then please start with an overview of flex.bi concepts and learn how to analyse and create reports. This tutorial will explain how to define new calculated members using MDX calculation formulas.
If you have never worked with MDX before, we suggest you watch this video: MDX basic concepts
Dimensions and hierarchies
...
If you would like to override in a measure value calculation some current context dimension member with a different member then you need to use a tuple of this measure and other dimension member (or several dimension members). MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get Store Sales measure value for all customers then in a formula you should use a tuple:
| Code Block |
|---|
( [Measures].[Store Sales], [Customers].DefaultMember ) |
This formula means that Store Sales should be calculated using all dimension members from the current context except Customers dimension for which default member (All Customers) should be used.
Tuples are frequently used to calculate percentage of a measure value from some total value. Let’s define a calculated measure [Measures].[Sales / customers total %] with a formula (and percentage formatting)
| Code Block |
|---|
[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].DefaultMember) |
When in report you will combine [Measures].[Sales / customers total %] with, for example, [Customers].[USA].[CA] then you will see percentage of California sales from the total customer sales.
...
So we can define a calculated measure [Measures].[Sales / parent customer %] with a formula:
| Code Block |
|---|
[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent) |
Please see also DefaultContext function documentation which can be used to override the context for evaluation of measures.
...