In this 40-minute video by Chris Webb you will see how common business calculations can be implemented in MDX.
There are two popular types of calculations in MDX. The first is the relative tuple pattern. In this pattern the CurrentMember function is used to return a member from a hierarchy; this member is then passed to another function such as PrevMember to find another member relative to that member. Subsequently, this new member is then used inside a dynamic tuple expression to return a value from the cube. Frequently used examples of this type of calculation are: previous period growth, and percentage share calculations.
To help with formatting, values returned by calculated members can have a number format applied to them with the Format_String property. Division-by-zero (or division-by-null) errors can be avoided either by using an IIF() to check the value you are dividing by, or, if you are using SSAS 2012 or newer, the Divide() function.
The second common calculation pattern is the relative set pattern. In this pattern the starting point is still the CurrentMember function, but this time it is used to construct a set expression which is then passed to a numeric function such as Sum() or Count(). A three-period moving average calculation is a good example of this pattern.
Watch this video to learn about these common calculations, and do not miss the next video in our MDX series, “More Member and Set Functions,” which covers other useful business calculation patterns.
