Tuesday, March 06, 2012 5:21 PMI 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.
Tuesday, March 06, 2012 8:00 PM
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]
Tuesday, March 06, 2012 8:41 PM
Let me assume you "Customer Hierarchy" as
State --> Zip --> Customer
Here is the MDX
Here is the customer age average by "State"
MEMBER [Measures].[Average Age] AS
) AS Set1,
} ON COLUMNS,
} ON ROWS
Hope this helps ,
Do not forget to mark as "Answered".