Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need MDX query to calculate weighted averages Expand / Collapse
Author
Message
Posted Thursday, July 17, 2008 3:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 2
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
Posted Friday, July 18, 2008 2:49 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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 columns
FROM
[Price]
WHERE
[Measures].[wavg];


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
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
Posted Wednesday, September 19, 2012 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 8:53 AM
Points: 6, Visits: 31
...
Post #1361553
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse