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’
IT Consultant on the edge 