SSAS Calculation is a Multidimensional Expressions (MDX) expression or script that is used to define a calculated member, a named set, etc., in a cube in Microsoft SQL Server Analysis Services. Here, I am going to give you some sample MDX expressions to do some typical calculations which are often required in providing business solutions using SSAS.
1) To calculate Cumulative Values (Order by Measure) :
If you want to calculate the cumulative value of a measure based on certain dimensional attribute (ordered by the Measure), you can use the following expression:
iif
(
[Measures].[Amount]> 0,
Sum (Head(Order ([DimRegion].[Area].Siblings,[Measures].[SaleAmount],BDESC) ,
Rank ([DimRegion].[Area].CurrentMember,Order ([DimRegion].[Area].Siblings,
[Measures].[SaleAmount],BDESC ))),
[Measures].[SaleAmount])
, null
)
The above expression will give us the Regional Area wise cumulative Sales Amount.
2) Similar to the above sample, If you want to calculate the Cumulative sum of a measure based on the Time dimension members, we can use the following expression
Sum(
{[DimTime].[Quarter].FIRSTCHILD:[Dim Time].[Quarter].CURRENTMEMBER},
[Measures].[SaleAmount]
)
The above expression will return you the Quarter wise cumulative Sale Amount.
3) To calculate Total (Sum ) value of a measure and return the same to all members of the attribute
iif
(
[Measures].[Expense Amount]> 0,
sum([DimRegion].[Area].PARENT, [Measures].[SaleAmount]),
null
)
4) To Calculate the Percentage to Total (Contribution to Total Sales by each reach region)
[Measures].[SaleAmount]/
MAX([DimRegion].[Area].Allmembers,[Measures].[ SaleAmount])*100
5) To Calculate Periodic Growth of Sales in SSAS calculations
SUM(
[DimTime].[Time_Hierarchy].CURRENTMEMBER,[Measures].[SaleAmount])-SUM(ParallelPeriod([DimTime].[Time_Hierarchy].[Year],1,[DimTime].[Time_Hierarchy].CURRENTMEMBER),[Measures].[SaleAmount]
)
6) Consider the above Calculated Member Name as [Growth], If you want to calculate What is the Periodic Growth Percentage of Sales, then we can use the following expressions:
(
[Measures].[Growth]/SUM(ParallelPeriod([Dim Time].[Time Hierarchy].[Year],1,
[Dim Time].[Time Hierarchy].CURRENTMEMBER),[Measures].[Sales Amount])
)
7) SSAS Calculation to calculate Last Year Values:
SUM
(
ParallelPeriod([Dim Time].[Time Hierarchy].[Year],1,
[Dim Time].[Time Hierarchy].CURRENTMEMBER),
[Measures].[Sales Amount]
)
8) SSAS Calculation for Year To Date (YTD) values
SUM
(
YTD([Dim Time].[Time Hierarchy]),
[Measures].[Sales Amount]
)
9) SSAS Calculation for Quarter To Date (QTD) values
SUM
(
QTD([Dim Time].[Time Hierarchy]),
[Measures].[Sales Amount]
)
10) SSAS Calculation for Month To Date (MTD) values
SUM
(
MTD([Dim Time].[Time Hierarchy]),
[Measures].[Sales Amount]
)
11) Previous Member value
To get value of the Previous Member:
Syntax:
Dimesion.Member.PREVMEMBER
Ex:
IIF([Dim Sales Stage].[Sales Stage].PREVMEMBER>0,
[Measures].[FactSalesCount]/Sum(([Dim SalesStep].[SalesStep].PREVMEMBER),[Measures].[FactSalesCount]),1)*100
(ie, the above example gives us the Percentage of current SalesStep’s count against previous sales step. The IIF is used to check as the previous member has any value- to avoid divide by zero issue)
12) Last Year Values
SUM(ParallelPeriod([Date].[Time_Hierarchy].[Year],1,[Date].[Time_Hierarchy].CURRENTMEMBER),[Measures].[SalesCount])
The above example will give us the last year sales count
