Tuesday, March 31, 2015

How do you write MDX query that returns measure ratio to parent value?

Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independant on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level [All]:
WITH MEMBER [Measures].[Order Count Ratio To Parent] AS
IIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0
, [Measures].[Order Count]
([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)
, FORMAT_STRING = "Percent"
SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0
, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]
} ON 1
FROM [Adventure Works]

No comments:

Post a Comment