"ContributionToParent" Calculation

Today, we will see a simple but important calculation which is often used in data analysis and that is “Percentage Contribution to its Parent” calculation. Let’s take an example where we have a state Maharashtra having 3 cities Mumbai, Pune and Nagpur where we need to analyze the sales data. Total sale of the state is say 1000$s for some random year, whereas total sale in Mumbai, Pune and Nagpur is 600$, 400$ and 200$ respectively so contribution of Mumbai for Maharashtra would be (600*100/1000) i.e. 60% and similarly for Pune and Nagpur would be 40% and 20% respectively.

Now we will see how we can simulate this scenario in SSAS 2008.

Let’s create a simple cube having following schema. (Assuming reader can create it very quickly without much guidance or can create their own test cube, concept would be the same!)

Now, we will create a calculated member, say ContributionToParent, please see below screen shot for configuring it –

Following MDX expression is used to evaluate the calculated member –

([Measures].[Sales]) / ([Measures].[Sales],[DimGeo].[DimGeo].CurrentMember.Parent)

You can try this on your own test cube and then can test data manually and then from cube browser, result would be the same!