Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Need MDX query to calculate weighted averages Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 17, 2008 3:10 PM
 SSCommitted Group: General Forum Members Last Login: Sunday, December 01, 2013 5:52 AM Points: 1,840, Visits: 3,556
 I have a simple fact table that doubles as a dim table (date, quarter form a hierarchy in the time dimension)ID price activity date quarter-----------------------------------------------------------------------------------1 100 10 1/1/2007 12:00:00 AM Q12 90 25 2/1/2007 12:00:00 AM Q13 81 32 3/1/2007 12:00:00 AM Q1........................................................................................................................................................................................................................................12 40 20 12/1/2008 12:00:00 AM Q4 To keep this simple, I have 12 records in the table one for each month of the year 2007 (four quarters).I want to calculate a weighted average of the price over the 12-month period based on quarter.For quarter 1, Q1, for example, that would be (based on the values above):(100*10 + 90*25 + 81*32)/3.Here, I have summed over the price*activity products for Q1 and divided by 3 (no. months per quarter).I need to come up with an MDX expression for a calculated member that does this on the fly per quarter.Here is my 1st clumsy attempt at this:Avg([Fact Price Activity].[Quarter], [Measures].[Activity]*[Measures].[Price])Well, it didn't work...Anyone know how to do this?
Post #536343
 Posted Friday, July 18, 2008 2:49 PM
 SSCommitted Group: General Forum Members Last Login: Sunday, December 01, 2013 5:52 AM Points: 1,840, Visits: 3,556
 Well, I have finally been able to come up with the right MDX query, but, although it works in SSMS, it gives me an error in the BIDS project (calculated member):MdxScript(Price) (9, 5) Parser: The syntax for 'WITH' is incorrect.Anyone have any ideas why this is happening? Here is the query (again it works fine is SSMS!):WITH MEMBER [Measures].[wavg] AS 'SUM( DESCENDANTS( [Fact Price Activity].[Hierarchy].CurrentMember, [Fact Price Activity].[Hierarchy].[Month]), [Measures].[Price]*[Measures].[Activity]) / COUNT( DESCENDANTS ( [Fact Price Activity].[Hierarchy].CurrentMember, [Fact Price Activity].[Hierarchy].[Month] ) )' SELECT [Fact Price Activity].[Hierarchy].[Quarter].MEMBERS ON columnsFROM [Price]WHERE [Measures].[wavg];
Post #537071
 Posted Wednesday, September 19, 2012 12:13 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 18, 2012 7:24 AM Points: 6, Visits: 26
 ...
Post #1361553

 Permissions