Page Comparison

{align:justified}If you would like to define new calculated member then expand corresponding dimension (e.g. _Measures_) and expand *Calculated members* section:{align}

{align:justified}When writing calculation formulas you will need to reference  dimensions, dimension hierarchies, hierarchy levels and other existing  dimension members. In MDX query language all names are enclosed in  square brackets \[   \]. \\
To reference *dimension* you just enclose its name in square brackets, e.g. [Customers], [Time] or [Measures]. \\
If dimension has just one *hierarchy* then you can reference the primary hierarchy in the same way as dimension, e.g. [Customers] or [Measures]. When you import _Time_ dimension then it automatically will create main hierarchy (with year, quarter, month and day levels) as well as _Weekly_ hierarchy (with year, week and day levels). [Time] will reference _Time_ dimension main hierarchy but [Time.Weekly] will reference _Time_ dimension _Weekly_ hierarchy. \\
When you expand specific dimension and expand *All hierarchy level members* then you see names of all main *hierarchy levels*. You can reference particular hierarchy level with [dimension or hierarchy name].[level name]. E.g. [Customers].[City] references _City_ level of main hierarchy of _Customers_ dimension. \\
In case of Time dimension use [Time].[Year], [Time].[Quarter], [Time].[Month], [Time].[Day] to reference main hierarchy levels and [Time.Weekly].[Year], [Time.Weekly].[Week], [Time.Weekly].[Day] to reference weekly hierarchy levels. \\
Each dimension typically will have default *{_}All{_}* *member* which can be used to get totals of measures per this dimension. If _Customers_ dimension has default _All_ member named _All Customers_ then you can reference it with [Customers].[All Customers]. As default _All_ member can be renamed it is safer to use [Customers].DefaultMember to get the same result. In case of Time dimension there are two default _All_ members for each hierarchy - [Time].DefaultMember and [Time.Weekly].DefaultMember. \\
When you want to reference *top level dimension members* then you use [dimension or hierarchy name].[member name]. E.g. [Customers].[USA] will reference USA member from top level _Country_ level. To reference _detailed level dimension members_\* you need to specify full "hierarchy path" to this member, e.g. [Customers].[USA].[CA] to reference _CA_ member in _State Province_ level under _USA_ parent member. Or [Customers].[USA].[CA].[San Francisco] to reference city etc. \\
All *measures* are in top level of _Measures_ dimension and you can reference them with e.g. [Measures].[Store Sales], [Measures].[Store Cost] etc.{align}

{align:justified}Now that you know how to reference other existing measures and other  dimension members we can create simple arithmetic calculations. \\
If we have [Measures].[Store Sales] and [Measures].[Store Cost] measures then we can define new calculated measure Profit (which will have full name [Measures].[Profit]) with formula:[Measures].[Store Sales] \- [Measures].[Store Cost]\\
When you will use this new measure Profit in your Standard-BI reports then for any combination with other dimension values this formula will calculate difference between Store Sales measure value and Store Cost measure value. \\
You can use defined calculated measures also in other calculated measures that you define later (_it  is important to define them in right sequence - you cannot use  calculated member that you will define later than current calculated  member_). For example, you can now define [Measures].[Margin %|Margin %] with formula[Measures].[Profit] / [Measures].[Store Sales]\\
and it will calculate margin as number from 0 to 1. If you would like to display results as percentage value then change _Formatting_ of this calculated measure to use _integer_ or _decimal percentage_ formatting. \\
You can perform arithmetic calculations also for calculated members in other dimension. For example, you could define [Customers].[West coast] calculated member in _Customers_ dimension with formula[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]\\
and now when you will combine in reports [Customers].[West coast] with [Measures].[Store Sales] you will get total sales for all these three states together. If you will combine it with [Measures].[Profit] you will get total profit for all these three states.{align}

{align:justified}When you use [Measures].[Store Sales] then it will calculate Store Sales measure value for other corresponding dimension members for each row /  column in report. If in your calculation formula you would like to  override some other dimension value then you need to use _tuples_ which is combination of members from different dimensions. 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 you would use tuple ([Measures].[Store Sales], [Customers].DefaultMember. \\
Let's define calculated measure [Measures].\[\] with formula (and percentage formatting)[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].DefaultMember) \\
When in report you will combine [Measures].\[\] with e.g. [Customers].[USA].[CA] then you will see percentage of California sales from total customer sales. \\
Maybe you don't want to see percentage from total sales but  percentage from customer hierarchy direct parent sales (e.g. for city  level show percentage from corresponding state sales). In this case you  can use *CurrentMember* dimension or hierarchy property  to access current member of corresponding dimension for which expression  currently is evaluated. E.g. [Customers].CurrentMember will return current member in _Customers_ hierarchy for which this expression is evaluated. Or in other words if _Customers_ dimension is placed on report rows then by using [Customers].CurrentMember we know for which row this formula is currently evaluated. \\
If we have 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 current member. Ancestor([Customers].CurrentMember, [Customers].[Country]) will move from current member up to "ancestor" in _Country_ level. \\
So we can define calculated measure [Measures].\[\] as[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent) \\ \\
 {align}

{align:justified}As 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 _Time_ dimension when you want to compare measures between different time periods or aggregate time period range. \\
For example, calculated measure [Measures].[Sales monthly growth] with formula[Measures].[Store Sales] \- ([Measures].[Store Sales], [Time].CurrentMember.PrevMember) \\
will calculate [Measures].[Store Sales] growth comparing to previous _Time_ dimension member period. If this calculated measure will be combined in report with month in _Time_ dimension it will show growth comparing to previous month, if it will  be combined with year then it will show growth comparing to previous  year. \\
There are several functions that help you "move in time":
* [Time].CurrentMember.PrevMember returns previous member in the same hierarchy level (it will return empty member for the first member)
* [Time].CurrentMember.NextMember will return next member
* [Time].CurrentMember.Lag(2) will return previous member with distance 2 (use any number for argument, Lag(1) is the same as PrevMember)
* [Time].CurrentMember.Lead(2) will return next member with distance 2 (Lead(1) is the same as NextMember)
* ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return "parallel" Time member one year ago (e.g. for day level member Jan 01 2012 it will be day Jan 01 2011 but for month level member Jan 2012 it will be month Jan 2011)
* OpeningPeriod([Time].[Day], [Time].CurrentMember) will return first descendant of current _Time_ member at _Day_ level (first day of year, quarter or month which is defined in _Time_ dimension)
* ClosingPeriod([Time].[Day], [Time].CurrentMember) will return last descendant of current _Time_ member at _Day_ level (last day of year, quarter or month which is defined in _Time_ dimension)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 member name as calculation formula result. For example, define calculated measure [Measures].[test opening day] with formulaOpeningPeriod([Time].[Day], [Time].CurrentMember).Name \\
and use it in report together with _Time_ dimension members and see if you get expected result for _Time_ dimension members at different levels. \\\\\\\\
 {align}

{align:justified}Now you know you to navigate to individual dimension members. But quite often you would like to perform operations on *set of dimension members*, for example, find sum or average value of some measure over selected set of dimension members. \\
The easiest way how to use sets in calculation formulas is to use list of members enclosed in curly braces, e.g.\{[Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA]\} \\
\{[Time].[2011], [Time].[2012]\} \\
If you would like to select set as range of sequential dimension  level members then you can specify first and last member and use : between them. For example, this will create a set of dates from Jan 01 2012 to Jan 15 2012:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 01 2012]:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 15 2012]\\
Quite often you don't want to specify exact range of members but  would like to get all dimension hierarchy level members. You can do it  with Members method, for example, to get all months in Time dimension:[Time].[Month].Members \\
When you want to test which members will be returned by some set expression then you can use SetToStr functions to create string of concatenated member full names. For example, define calculated measure [Measures].[test all months] with formulaSetToStr([Time].[Month].Members) \\
There are several other useful functions for working with sets, here are some examples how to use them:
* [Customers].[USA].[CA].Children returns set of children members using dimension hierarchy (in this example all cities in California)
* Descendants([Customers].[USA], [Customers].[City]) returns set of member descendants at specified hierarchy level (in this example all cities in USA)
* [Customers].[USA].[CA].[San Francisco].Siblings returns  all members which have the same parent as this member (in this example  all cities in California), it is the same as using [Customers].[USA].[CA].[San Francisco].Parent.ChildrenThere are additional methods that you can use to get just first or last members of these sets - FirstChild, LastChild, FirstSibling, LastSibling. \\\\\\\\\\\\\\\\\\\\\\\\\\
 {align}

{align:justified}Now that you know how to select different sets of dimension members  you can calculate different aggregated values from these sets:
* Sum(set, numeric_expression) calculates _numeric_expression_ for each _set_ member and returns sum of all these results. For example, Sum(LastPeriods(3). [Measures].[Store Sales]) will calculate total sales for last three _Time_ periods starting from current _Time_ dimension member.

* Count(set) returns count of set members. Count(set, ExcludeEmpty) will return count of set members for which corresponding measure values are not empty.

* Avg(set, numeric_expression) calculates average of _numeric_expression_ in

 _set_
* Max(set, numeric_expression) returns maximum value of _numeric_expression_ in

 _set_
* Min(set, numeric_expression) returns minimum value of _numeric_expression_ in _set_ \\\\\\\\\\\\\\
 {align}

{align

:justified

}You can write formulas with conditions and different results based on condition evaluation using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for exampleIIF([Measures].[Profit] > 0, 'Profit', 'Loss')

 \\
If there are many conditions then it is easier to use CASE function, for exampleCASE

 \\
WHEN [Measures].[Profit] > 1000 \\
THEN 'Big profit' \\
WHEN [Measures].[Profit] > 0 \\
THEN 'Small profit' \\
ELSE 'Loss' \\
END \\
If all conditions are comparisons of the same expression to different expected values then other CASE form can be used, for exampleCASE [Time].CurrentMember.Level.Name

 \\
WHEN 'Month'

 \\
THEN Sum(LastPeriods(3), [Measures].[Store Sales])

 \\
WHEN 'Day'

 \\
THEN Sum(LastPeriods(90), [Measures].[Store Sales])

 \\
END \\
In IIF and CASE conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as AND, OR and NOT operators as well as several specific operators:
* IS returns whether two objects are the same, for example, [Customers].CurrentMember IS [Customers].DefaultMember (which will be true if _Customers_ current member is default _All Customers_ member)
* IN and NOT IN returns whether member is in set, for example [Customers].CurrentMember IN [Customers].[USA].[CA].Children \\\\\\\\\\\\\\\\\\\\\\\\\\
 {align}

{align

:justified

}It is possible to write comments in calculation formulas. Use  comments either to describe some non-obvious complex calculations or  also commenting is valuable during writing and testing calculation  formulas - when something is not working as expected then comment all  formula lines and leave uncommented just some part of formula that you  would like to debug.-\- one line comment

 \\
expression -- comment until end of line \\
/\* multi line \\
comment \\
\*/ {align}