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
...
When writing calculation formulas you will need to reference dimensions, dimension hierarchies, hierarchy levels and other existing dimension members. In the MDX query language all names are enclosed in square brackets Highlight
| color | gray |
|---|
[]. To reference a dimension you just enclose its name in square brackets, e.g. [Customers], [Time] or [Measures].
...
When you expand the All hierarchy level members then you see names of all the main hierarchy levels. You can reference a particular hierarchy level with [dimension or hierarchy name].[level name]. E.g. [Customers].[City] references the City level of the main hierarchy of the Customers dimension.
When referencing the Time dimension use [Time].[Year], [Time].[Quarter], [Time].[Month], [Time].[Day] to reference the main hierarchy levels and [Time].[Weekly].[Year], [Time].[Weekly].[Week], [Time].[Weekly].[Day] to reference the weekly hierarchy levels.
Each dimension typically will have a default All member which can be used to get totals of measures per this dimension. If the Customers dimension has a default All member named a All Customers then you can reference it with [Customers].[All Customers]. Since the default All member can be renamed it is safer to use [Customers].[DefaultMember] to get the same result. The time dimension has two default All members for each hierarchy - [Time].[DefaultMember] and [Time].[Weekly].DefaultMember.
When you want to reference the top level dimension members then you have to use the following format - [dimension or hierarchy name].[member name]. E.g. [Customers].[USA] will reference the USA member from the top level of the Country level. To reference detailed level dimension members* you need to specify the full "hierarchy path" to this member, e.g. [Customers].[USA].[CA] to reference the CA member in the State Province level under the USA parent member. Or [Customers].[USA].[CA].[San Francisco] to reference a city etc.
All measures are in the top level of the Measures dimension and you can reference them with e.g. [Measures].[Store Sales], [Measures].[Store Cost] etc.
Simple arithmetic calculations
...