Page Comparison

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

...

There is a default hidden [Measures].[Fact Count] measure which will return number of fact rows in a cube database table. You can use it to calculate simple average values, for example, define [Measures].[Average Store Sales] as 

[Measures].[Store Sales] / [Measures].[Fact Count]

Tuples

When you use [Measures].[Store Sales] in a formula then it will calculate Store Sales measure value for the current context of each report cell. Current context includes corresponding row and column dimension members of the report cell as well as selected page dimension members.

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:

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)

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.

Maybe you don’t want to see a percentage from the total sales but a percentage from customer hierarchy direct parent sales (e.g. for City level show percentage from corresponding State sales). In this case you can use the CurrentMember dimension or hierarchy property to access the current context dimension member. For example, [Customers].CurrentMember will return the current member in the Customers dimension for which the formula is evaluated. Or in other words if Customers dimension is placed on report rows then by using [Customers].CurrentMember we know for which row the formula is evaluated.

If we have the current member then we can navigate to other dimension members relative from this member. [Customers].CurrentMember.Parent will return parent member in Customers hierarchy for the current member. Ancestor([Customers].CurrentMember, [Customers].[Country]) will move from current member up to “ancestor” in Country level.

So we can define a calculated measure [Measures].[Sales / parent customer %] with a formula:

Please see also DefaultContext function documentation which can be used to override the context for evaluation of measures.

Moving across dimension levels

...