March 3, 2016 at 8:46 am
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]
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply