Calculations tab - MSAS 2005

Hi

Pretty new to this so bear with me:)

I have created a cube which has atm a single value measure and 5 dimensions which enable me to look at totals of the value measure in various ways - quite pleased I've managed to get this far without too many headaches.

I now need to create a new measure which will be the existing value measure didvided by a subset of the existing value measure. So lets say each value belongs to a group numbered 1-10. I need to have a new value which is 'value' divided by the total of values belonging to group 1. Hope thats clear :)

1. I think I need to create a new calculated member in the calculations tab of MSAS 2005, is that right?

2. Can I have an example of syntax how to do this? I know SQL very well but MDX not at all so knowing what I want but not how to get at it is very frustrating.

Thanks for any help you can give.

Wayne

Hi

Ad 1) Yes

Ad 2) Assuming that your "group" is an attribute in one of your dimensions, the following should work.

CASE WHEN ([Measures].[Value],[Dimension 1].[Group].[Group 1]) = 0
THEN NULL
ELSE [Measures].[Value]/([Measures].[Value],[Dimension 1].[Group].[Group 1])
END

And also you should set the "Non empty behavior" of the new calculated measure to [Measures].[Value].

|||

Couple of comments:

1) IIF is often preferable to CASE for performance reasons

2) For this calculation it is not possible to set NON_EMPTY_BEHAVIOR correctly in general case. Setting it to [Measures].[Value] will cause wrong results when [Measures].[Value] is 0 (as opposed to NULL). If you are sure it is never 0, then probably it will work.

|||

Thanks for those replies.

I've created a calculation but I'm not quite getting what I want yet. I'll flesh out my scenario a bit & maybe you can help some more :)

I am working with accounts data & each value in my cube is held against an accounting code. Each accounting code belongs to a hierarchy of groups which is 3 deep - I call them lvl1, lvl2 and lvl3.

I have data for many businesses which spans several years.

In order to compare one business' performance to another I need to divide each value in the cube by each ￡1000 of income for that business in that year. The income values have a lvl3 code of 'INCOME'. This is the bit I am having trouble with.

So my calculation as it stands atm is:

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS

CASE WHEN ([Measures].[Val],[Tbl Acc Codes].[Acc Lvl3 Code].[INCOME]) = 0

THEN 0

ELSE [Measures].[Val] /(([Measures].[Val],[Tbl Acc Codes].[Acc Lvl3 Code].[INCOME]) / -1000)

END

,

VISIBLE = 1 ;

When I look at the cube browser this seems to be giving me a ratioval on most of my group total lines but not for every value in the cube as I would like. I'm thinking that the income total is perhaps being calculated depinding on the position of each Val within the cube, when what I need is for the calculation to use the same income total for each business/year every time. So if a business has a total income of ￡25,000 for 2005 I want to divide every value in my cube for that business/year by 25.

Any help you can offer much appreciated & sorely needed :)

Thanks