Need MDX query to calculate weighted averages

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Need MDX query to calculate weighted averages...

Need MDX query to calculate weighted averages

Rate Topic

Display Mode

Topic Options

Author

Message

Marios Philippopoulos

Posted Thursday, July 17, 2008 3:10 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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 Q1
2 90 25 2/1/2007 12:00:00 AM Q1
3 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?

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #536343

Marios Philippopoulos

Posted Friday, July 18, 2008 2:49 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

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? Sad

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 columns
FROM
[Price]
WHERE
[Measures].[wavg];

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #537071

rc_stone_1

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

« Prev Topic | Next Topic »

Permissions