MDX - Sum Members on a dimension level query

• jon.lunn

  Old Hand

  Points: 306

  More actions

  March 3, 2016 at 8:46 am

  #147538

  Hi,

  I'm having a bit of a headache with summing values using SUM and AXIS, to correctly work out a percentage moving on to a next stage in a exam program

  This is what i'm getting

  

  I'm trying to get AXIS to just SUM on the age range to work out the percentage of people who have a Pass grade, compared the the full total of the Pass & Failed

  I can get there using the MEMBER TestTypeTotal, but wanted it independent of having to Declare the SET's for the Previous and Current Stages, and need the MEMBER [UsingAxis] to return the same values as the [TestTypeTotal]. I've tried a few ways not quite there. If there is a better way, then I would like to know, Thanks!

  WITH

  SET [PreviousStage] AS{[PreviousStage].[StageName].&[School]

  ,[PreviousStage].[StageName].&[Employment]

  }

  SET [CurrentStage]AS{[CurrentStage].[StageName].&[Exam]

  }

  SET [NextStage] AS{[NextStage].[StageName].&[Pass]

  }

  MEMBER [Measures].[AllPeople] AS AGGREGATE([Age Range].[Age Group].[All], [Measures].PersonCount])

  MEMBER [Measures].[TestTypeTotal] AS [Measures].[PersonCount]

  / AGGREGATE (

  ([NextStage].[StageName].[All]

  ,[PreviousStage]

  ,[CurrentStage]

  )

  ,[Measures].[PersonCount]

  ), FORMAT_STRING = 'Percent'

  MEMBER [Measures].[PercentNextStage] AS DIVIDE([Measures].[PersonCount], [Age Range].[Age Group].[All], [Measures].PersonCount])

  MEMBER [Measures].[UsingAxis] AS SUM(AXIS(1), [Measures].[AllPeople])

  SELECT{[Measures].[PersonCount]

  ,[Measures].[AllPeople]

  ,[Measures].[TestTypeTotal]

  ,[Measures].[PercentNextStage]

  ,[Measures].[UsingAxis]

  }

  ON COLUMNS,

  FILTER(

  ([Report Date].[Standard Calendar].[Month].&[201601]

  ,[PreviousStage]

  ,[CurrentStage]

  ,[NextStage]

  )

  *[Age Range].[Age Group].[Age Group]

  ,[Measures].[PercentNextStage] > 0

  )

  ON ROWS

  FROM

  [Dev Cube]