Calculated measures in MDX not working

0
votes

I wanted to have results something like this:

city zip revenue x

A | 1 | 2000 | 10000

A | 2 | 3000 | 10000

A | 3 | 5000 | 10000

B | 6 | 1500 | 3000

B | 9 | 1500 | 3000

To get the results, I wrote this mdx statement, but revenue and X are coming out same. I thought the revenue would be aggregated over city only and for each zip the value would be the same because the city associated with the zip is same. I wonder if any body can help me out here.

with member [Measures].[X] as '([Location].[City].currentmember, [Measures].[Revenue])'

SELECT NON EMPTY { Measures.[Revenue], [Measures].[X]} ON COLUMNS, NON EMPTY { ([Location].[City].[City]*[Location].[zip].[zip] ) } ON ROWS FROM [State Revenue]))

Regards

1
ssasmdx

1 Answers

1
votes

You have to specify explicitly that you need the aggregated total for the zip code. Try adding the DefaultMember of the [zip] hierarchy in the tuple, as follows:

with member [Measures].[X] as '([Location].[City].currentmember, [Location].[Zip].DefaultMember, [Measures].[Revenue])'

SELECT NON EMPTY { Measures.[Revenue], [Measures].[X]} ON COLUMNS, NON EMPTY { ([Location].[City].[City]*[Location].[zip].[zip] ) } ON ROWS FROM [State Revenue]))

The DefaultMember function represents the (All) member unless specified otherwise.