none
Calculation based on Dimension Attribute

    Question

  • I have a Customer dimension with Age, State, Zip Code and other attributes, What would be the MDX expression to calculate the average age of a customer by state, Zip Code or any other dimension attribute? The Measure Group has only one measure, which is the count of customers. The calculation would be: SUM(Customer Age)/Customer Count. Please post the MDX expression.

    Max Long

    Tuesday, March 06, 2012 5:21 PM

Answers

  • Hi Max,

    Let me assume you "Customer Hierarchy" as

    State --> Zip --> Customer

    Here is the MDX

    Here is the customer age average by "State"

    WITH
    MEMBER [Measures].[Average Age] AS
    AVG
        (
        DESCENDANTS
            (
            [Customer].[Customer Hierarchy].Currentmember,
            [Customer].[Customer Hierarchy].[Customer]
            ) AS Set1,
            StrToValue(Set1.Current.Properties("Age"))
        )

    SELECT
        {
        [Measures].[Average Age]

        } ON COLUMNS,
        {
        [Customer].[Customer Hierarchy].[State]

        } ON ROWS
    FROM
        [Your Cube]

    Hope this helps ,

    -Ashim

    Do not forget to mark as "Answered".

    • Proposed as answer by AshimM Tuesday, March 06, 2012 8:42 PM
    • Marked as answer by Jerry NeeModerator Thursday, March 22, 2012 10:33 AM
    Tuesday, March 06, 2012 8:41 PM

All replies

  • Hi Max,

    Do you have the Age as an attribute? Then it would be a string for Analysis Services, making it more complex but not impossible to calculate this. This would involve type conversions, and would be very inefficient.

    The easiest way would be to add the age as a measure to the cube, maybe call it [Summed Age]. In order not to confuse the users, you would make this measure invisible, and add a calculated measure in the calculation script e. g. as follows:

    CREATE MEMBER CURRENTCUBE.[Measures].[Average Age] AS [Measures].[Summed Age] / [Measures].[Customer Count]

    Frank

    • Proposed as answer by AshimM Tuesday, March 06, 2012 8:41 PM
    • Unproposed as answer by AshimM Tuesday, March 06, 2012 8:42 PM
    Tuesday, March 06, 2012 8:00 PM
  • Hi Max,

    Let me assume you "Customer Hierarchy" as

    State --> Zip --> Customer

    Here is the MDX

    Here is the customer age average by "State"

    WITH
    MEMBER [Measures].[Average Age] AS
    AVG
        (
        DESCENDANTS
            (
            [Customer].[Customer Hierarchy].Currentmember,
            [Customer].[Customer Hierarchy].[Customer]
            ) AS Set1,
            StrToValue(Set1.Current.Properties("Age"))
        )

    SELECT
        {
        [Measures].[Average Age]

        } ON COLUMNS,
        {
        [Customer].[Customer Hierarchy].[State]

        } ON ROWS
    FROM
        [Your Cube]

    Hope this helps ,

    -Ashim

    Do not forget to mark as "Answered".

    • Proposed as answer by AshimM Tuesday, March 06, 2012 8:42 PM
    • Marked as answer by Jerry NeeModerator Thursday, March 22, 2012 10:33 AM
    Tuesday, March 06, 2012 8:41 PM