I am posting this question for the second time, Please help me.

Hi i have problem which i think can be solved using calculated cells.
I have a fact table which stores three measures x, y, z.
I have two dimensions A and B. The measure z is rolled up as a sum.
The dimension B has two levels:- LC and activity. When i view the measure z
at the LC level in Dimension B it should not be a simple sum of all the z
values for all activity under the level LC, rather it should be a function of
measure x and y. I will tell you an example.
if
x=0 then z=y
x=100 then z=z
if 0<x<100 the z=(100/x) * z
suppose the following are the activity under a level

Activity x y z (required z)
aaa 0 20 15 20
bbb 100 35 45 45
ccc 40 30 20 50
ddd 75 15 30 40
Total 110 155
When i view z at the lc level it should show me 155 and not 110
I think it can be done using calculated cell. But it is somehow not working.
Can some one help me in this.

Thanks in advance.

Here's an example from Foodmart Sales that's similar to your scenario.
The calculated cell for [Store Sales] (z) depends on [Sales Count] (x)
and [Store Cost] (y):

for '({[Measures].[Store Sales]})'
as 'Sum(Descendants([Store].CurrentMember, [Store].[Store Name]),
iif([Measures].[Sales Count] < 5000,
[Measures].[Store Cost], iif([Measures].[Sales Count] < 8000,
(8000 * CalculationPassValue([Measures].[Store Sales], -1,
RELATIVE))/[Measures].[Sales Count],
CalculationPassValue([Measures].[Store Sales], -1, RELATIVE))))',
FORMAT_STRING = 'Currency'

select {[Measures].[Unit Sales], [Measures].[Store Cost],
[Measures].[Store Sales], [Measures].[Sales Count]} on columns,
Non Empty Descendants([Store].[All Stores].[USA].[CA],
[Store].[Store Name], SELF_AND_BEFORE) on rows
from Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!