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
Hello Nalangkilli,
ReplyDeleteCould you give me an example about data for each month.
My scenario is I want to get stock on-hand of product as below: opening balance, araising and ending balance for each month.
Thank you very much for your suggesstion
Hi,
ReplyDeleteYou can use the following MDX functions:
OpeningPeriod() and ClosingPeriod()
Syntax:
[Measure].[OpeningStock] = (Measures.[StockQuantity], OpeningPeriod([Month]))
[Measure].[ClosingStock] = (Measures.[StockQuantity], ClosingPeriod([Month]))
But in the real time, You can face a problem while calcualting the closing Stock for the current period.
ie, The ClosingPeriod () function will traverses the members' hierarchy tree to find the last leaf descendant under the given member. In the real time scenarios, Time Dimension will have the values for the future period. But this function will not look into whether the last period is future or not. And it will return Null value when the current date is mid of the month and the data is available only till the current date.
To handle the above scenario, We can use the follwing method :
1. Create a Calcualted member to identify last non empty value :
Measures.[LastNonEmptyStockVal] =
IIf(IsEmpty((Measures.[StockQuantity], Time.CurrentMember) ,
(Measures.[LastNonEmptyStockVal],Time.CurrentMember.PrevMember),Measures.[StockQuantity]))
2. And then calculate the Closing Stock :
Measures.[ClosingStock] =
(Measures.[LastNonEmptyStockVal], ClosingPeriod([Month]))
Hope this will help you.
Regards,
Nalangkilli.
This will really helpful for developers. fantastic work.
ReplyDeleteCould you please help me , how to use value column property in ssas ..
ReplyDeleteThanks..!!
Nalangkilli thank you this is what I was looking for
ReplyDelete