MDX and Sum columns

To be honest this post is just to remind myself on how to do stuff!
I was trying to work out the through put of people going through a stages of a program and got stuck on how to SUM a column in MDX.

Current Program
Next Program
No of People
Sum of People
Percentage To Next Stage
Program 1
Program 2
152
310
49.0%
Program 1
Program 3
68
310
21.9%
Program 1
Program 4
47
310
15.2%
Program 1
Program 5
33
310
10.6%
Program 1
Program 6
10
310
3.2%
 
Total
310
 N/A
100.0%
This is what i was looking for, to see the %age of people moving to the next stages. How did I get the Sum of ‘No of People’? Well I used the MDX SUM function with a AXIS function thrown in as well.
MEMBER [Measures].[Sum of People] AS SUM(AXIS(1), [Measures].[No of People])
So the ‘Percentage To Next Stage’ was easy
MEMBER [Measures].[Percentage To Next Stage] AS [Measures].[No of People] / [Measures].[Sum of People], FORMAT_STRING = ‘Percent’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s