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

• 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

Hope this helps ,

-Ashim

Do not forget to mark as "Answered".

• Proposed as answer by Tuesday, March 06, 2012 8:42 PM
• Marked as answer by 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 Tuesday, March 06, 2012 8:41 PM
• Unproposed as answer by 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