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
[928 byte] By [
Waynest] at [2007-12-19]
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
