posted 6/29/2011 by DustinRyan - Views: [4136]
If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:
This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn't make sense and isn't something you'd do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:
WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on 0, [Date].[Calendar].[Date] on 1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]
If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.
WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format="Currency" Select [Measures].[YTD Average Price] on 0, [Date].[Calendar].[Date] on 1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]
Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.
Nice post. Looks like a pretty familiar problem to me ;)
Can I please enquire about the comments previously posted by Boyan Penev? They seem to have been removed and I found they were very insightful in clarifying why your top query (which appears to be a little nonsensical) was incorrect. eg the first (top) query appears to be attempting to aggregate averages which does not make sense.
Well I removed them because that wasn't the point of the post. The only thing to take away from the first query is that it errors. Of course it doesn't make sense, but thats not the point.