In this post I’ll conclude what I began in an earlier blog post covering ten of the most common cube calculations implemented for some of my clients. You can read the first blog post in this 2 part series here, which covers the first five calculations (in no particular order). As before, these calculations will be written against the Adventure Works 2008 R2 sample AS project.

5. Percent of a Parent

This is very common calculation that I’ve seen used countless times to determine what percentage of a measurement makes up the total measurement for a larger body. In this example, I’m calculating what percent of Internet Sales for a product make up the total Internet Sales for the product’s Sub Category.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percent of Parent]
AS
Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Internet Sales Amount]
)
Then Null

// Test for current coordinate being on the (All) member.
When [Product].[Product Categories].CurrentMember.Level Is
[Product].[Product Categories].[(All)]
Then 1

Else ( [Product].[Product Categories].CurrentMember,
[Measures].[Internet Sales Amount]  )
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )

End
,
FORMAT_STRING = “Percent”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

4. Rolling Average Calculation

Another common calculation used is one that calculates an average over the period of time, such as the monthly average of the past 12 months.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Monthly Average Over Year]
AS

    Avg
(
[Date].[Month Name].CurrentMember.Lag(11) :
[Date].[Month Name].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

3. Percentage Growth From Previous Period

If you’re wanting to measure the percentage of growth of a measure from a previous period, this is the calculation to use. A couple things to keep in mind: This calculation is hierarchy specific and is currently set up to measure the percentage of growth from the previous calendar year.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Growth From Previous Period]
AS Case
// Test for current coordinate being on (All) member.
When [Date].[Calendar].CurrentMember.Level Is
[Date].[Calendar].[(All)]

Then “NA”

// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
Then Null

Else (
( [Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount])
–
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
/
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
End,
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

2. Period to Date Average

This calculation is very similar to #4 up above but instead of giving you the average Internet Sales for the past 12 months, the calculation will return the average Internet Sales for the current period. For example, if you view this calculation at the day level, you will see the average daily Internet Sales for the current month.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Over Current Period]
AS

Avg
(
[Date].[Calendar].CurrentMember.FirstSibling :
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

1. Top Products Percent of Sales

The last calculation I have to show you utilizes a named set to return to the top 10 best selling products. Many clients like to monitor the sales of their best selling products this way.

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Internet Products]
AS TopCount
(
(

         [Product].[Product].Children
)
,10
,[Measures].[Internet sales amount]
), DISPLAY_FOLDER = ‘Sets’  ;   

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount]),
FORMAT_STRING = “CURRENCY”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

You can also add a calculated measure to show the percentage of sales of those 10 best selling products.

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Percent of Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount])/
[Measures].[Internet Sales Amount],
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

If you have any other calculations which you think should be on this list and would love to share them, please send them my way. I’d love to see ‘em!

If you don’t consider yourself an MDX expert or are looking to eliminate some of the calculation development time, I’d highly suggest checking out BI xPress. BI xPress has a great feature built in that allows you to quickly and efficiently add calculations and named sets to your cube, all without the knowledge of MDX. Heck, most of the calculations in this blog post were written in about 10 seconds with the BI xPress Calculation Builder.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

This will open the MDX Calculation Builder Wizard. Select the Set template you’d like to use. I’m selecting the Top 10 Count template.

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

Lastly, select the measure.

And its really that easy.

To download the free trial of BI xPress, head over to PragmaticWorks.com and check out BI xPress. You won’t regret it after you see how much time it will save you with your SSAS and SSIS development.