I am trying to set up a calculated member in analysis services that
calculates differently dependant on the selected dimension value.

For example.

If I have a calculated measure of say PercentageSold that is calculated from
my two measures Sold and Available. Then I would like to be able to exclude
any sold items that fell into the dimension SellType of 'Complimentary'.

The basic pseudo code is something like IIF(SellType = 'Complimentary', 0 ,
Sold/Available )

I have tied to use Filter, Except, and IIF in my MDX to no avail.

Please note that other calculations do include any sold items or any
available items, and so it is not a case of simply scrubbing the data. I
know that I can fudge it by having an extra column in the data that just
shows Non Complimentary values, but this doesn't seem a very clean solution.

Any help would be much appreciated.

Thanks in advance

Regards
Tony

Assuming that [Measures].[Sold] has sum/count aggregation:
'([Measures].[Sold]
- ([Measures].[Sold],[SellType].[Complimentary]))
/[Measures].[Available]',
FORMAT_STRING='PERCENT'

- Deepak

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