Need MDX query to calculate weighted averages

  • 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

    -----------------------------------------------------------------------------------

    1100 10 1/1/2007 12:00:00 AM Q1

    290 25 2/1/2007 12:00:00 AM Q1

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

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • 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];

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • ...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply