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

...

[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.

...

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

Moving

...

in time

As you can move to a different dimension hierarchy levels you can also move to a different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate a time period range.

For example, a calculated measure [Measures].[Store Sales ] it will calculate the value of the Store Sales measure for other corresponding dimension members for each row / column in report. If you would like to override some other dimension value in your calculation formula then you need to use tuples. Tuples are a combination of members from different dimensions. The MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get the Store Sales measure value for all customers then you would use the tuple (monthly growth] with a formula:

[Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember)

will calculate a [Measures].[Store Sales], [Customers].DefaultMember.

 Let's define a calculated measure [Measures].[Percent] with the formula (and percentage formatting) [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].[DefaultMember]).

 When you combine [Measures].[Percent] with e.g. [Customers].[USA].[CA] then you will see the percentage of California sales from total customer sales in the report.

 Maybe you don't want to see the percentage from total sales, but the percentage from the customer hierarchies direct parent sales (e.g. for the city level show the percentage from corresponding state sales). In this case you can use the CurrentMember dimension or hierarchy property to access to the current member of a corresponding dimension for which the expression is currently evaluated. E.g. [Customers].CurrentMember will return the current member in the Customers hierarchy for which this expression is evaluated. Or in other words if the Customers dimension is placed in rows section then by using [Customers].CurrentMember we know for which row this formula is currently being evaluated.

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

 So we can define the calculated measures [Measures].[Percent] as [Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent)

...

 growth comparing to the previous Time dimension member period. If this calculated measure will be combined in a report with a month in the Time dimension then it will show a growth comparing to the previous month. If it will be combined with a year then it will show the growth comparing to the previous year.

There are several functions that will help you “to move in time”:

• [Time].CurrentMember.PrevMember returns the previous member, which contains data, in the same hierarchy level (it will return an empty member for the first member)
• [Time].CurrentMember.NextMember will return the next member
• [Time].CurrentMember.Lag(2) will return the previous member with a distance 2 (use any number for the argument, Lag(1) is the same as PrevMember)
• [Time].CurrentMember.Lead(2) will return the next member with a distance 2 (Lead(1) is the same as NextMember)
• ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return the “parallel” Time member one year ago (e.g. for the Day level member Jan 01 2012 it will be the day Jan 01 2011 but for the Month level member Jan 2012 it will be the month Jan 2011)
• OpeningPeriod([Time].[Day], [Time].CurrentMember) will return the first descendant of the current Time member at the Day level (the first day of a year, a quarter or a month which is defined in the Time dimension)
• ClosingPeriod([Time].[Day], [Time].CurrentMember) will return the last descendant of the current Time member at the Day level (the last day of a year, a quarter or a month which is defined in the Time dimension)

If cube has only one Time dimension then the [Time].CurrentMember argument can be omitted as it will be used by default. So you can use shorter expressions ParallelPeriod([Time].[Year], 1), OpeningPeriod([Time].[Day]), ClosingPeriod([Time].[Day]).

Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use .Name member property to get a member name as a calculation formula result. For example, define calculated measure [Measures].[test opening day] with a formula:

OpeningPeriod([Time].[Day], [Time].CurrentMember).Name

and use it in report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.

Just like you can move to different dimension hierarchy levels you can also move to different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate time period range.

...