Calculation based on Dimension Attribute

# Calculation based on Dimension Attribute

• Tuesday, March 06, 2012 5:21 PM

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

### All Replies

• Tuesday, March 06, 2012 8:00 PM

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 Tuesday, March 06, 2012 8:41 PM
• Unproposed As Answer by Tuesday, March 06, 2012 8:42 PM
•
• Tuesday, March 06, 2012 8:41 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