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

Answer 1

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

Answer 2

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".