MDX question - comparing values for individual member against average for whole population

Hi,
I need some MDX help.
I've got an AS 2005 cube that includes a dimension called "Company" consisting of a number of different companies. Within my cube I need to be able to show measures for individual companies against the same measures for the whole market (i.e. all companies), both the total and the average. Note: I need to be able to do this for any measure within my cube.

e.g.

Company Name Company Total Market Total Market Average
BOB'S COMPANY 100 5000 150
ERIC's COMPANY 200 5000 150

I thought I might be able to implement this by creating an extra dimension called, say, "AmountType" with a default member of "Company Total" hardcoded into the underlying fact view. I could then create a calculated member for this dimension called "Market Total" which could be used to derive the total for the whole population. The following illustrates the type of MDX statements that I am trying (the set that I am using in the sum function includes a set and a tuple - this is valid isn't it?):

sum({Company.ALLMEMBERS, [Amount Type].&[Company Total]}, [Measures].CurrentMember)

Am I on the right lines. Can anyone tell me the sort of MDX i need to get this to work?

Thanks

George

George,

I think I understand what you have done, but just to make sure I will go over the steps I think you have taken and outline a solution that should work for you.

You have a dimension called "AmountType" that has a single member coming from your data source view called "Company Total". One thing that you should do is to make sure that the properties for this attribute hierarchy are set so that the "All" member does not show up if you are using the dimension soley as a placeholder for calculated members. This can be done by openning the dimension in the dimension editor, clicking on the attribute hierarchy and then setting the "IsAggregatable" property to false. You should now have a dimension with one member: "Company Total". Do you have an "All" member level defined for your "Company" hierarchy? I am guessing that you do and the calculations I will provide would have to be modified if you don't. Once you have the "AmountType" dimension rebuilt you can try adding the following calculations to your script:

CREATE MEMBER CURRENTCUBE.[AmountType].[AmountType].[Market Total]

AS

(Company.Company.DefaultMember,[AmountType].[AmountType].DefaultMember),

FORMAT_STRING = "Standard",

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time].[Period Calculation].[Market Avg]

AS

[AmountType].[AmountType].[Market Total] / Company.Company.Members.Count,

FORMAT_STRING = "Standard",

VISIBLE = 1;

Let me know if I misunderstood something or this MDX gives you values different from what you are looking for.

HTH,

- Steve