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 post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy