Hi all,

I am trying to perform a calculation which is causing me a headache because
I can't write MDX! - any help would be appreciated here.

Basically, I have the following scenario of data:

Date Product Value Qty1 Qty2
1/1/05 ProductA £10 2 100
1/1/05 ProductB £15 5 100
1/1/05 ProductC £5 4 100
1/1/05 ProductD £30 7 100
1/1/05 ProductE £40 5 100

2/1/05 ProductA £10 2 250
2/1/05 ProductB £15 5 250
2/1/05 ProductC £5 4 250
2/1/05 ProductD £30 7 250
2/1/05 ProductE £40 5 250

3/1/05 ProductA £10 2 500
3/1/05 ProductB £15 5 500
3/1/05 ProductC £5 4 500
3/1/05 ProductD £30 7 500
3/1/05 ProductE £40 5 500

I want to be able to perform the sum value/Qty2, but i only want to perfom
the division on one of the Qty2 values, plus, when i aggregate, I dont want
the values to sum, i.e. producing a value of 500 for the data for 1/1/05. It
should remain as 500 at any level of slicing and the 'Value' should always
perform the calcualtion at any level of a dimension.

Does this make sense? Sorry for the bad explanation, but i am new to AS and
have been reall thrown in at the deep end as apparently DBA's should know
everything!

Any help is appreciated.

Regards
Immy

Hi Immy,

It sounds like you have a granularity issue here. If I understand
correctly Qty2 is not dependant on any given product. In this situation
you would normally split the data into 2 cubes.

Cube 1
======
Dimensions: Date, Product
Measures: Value, Qty1

Cube 2
======
Dimensions: Date
Measures: Qty2

You would then create a virtual cube with both dimensions and all the
measures that joins the 2 cubes together. You would then create a
calculated measure with a different name (eg Qty2a) using ValidMeasure
(measures.Qty2). This will return a value for Qty2a regardless of what
was selected on the product dimension.

That is just a brief explanation of the recommended way of handling you
situation. If you look up Virtual Cubes and the ValidMeasure function in
BOL, it will give you more information.

That said, there may be hack that you could employ to get the result you
are after.

({Product.CurrentMember,,LEAVES).Item(0)},Measures .Qty2)
What the above does is to sum the Value for the current position. It
then uses the descendants function to navigate down the product
hierarchy to the leaf level. Then it arbitrarily grabs the first product
from this set of descendants and uses it's corresponding Qty2 value.

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <efP6fAR6FHA.2616 (AT) TK2MSFTNGP15 (DOT) phx.gbl>,
imtiaz_ullah (AT) hotmail (DOT) com says...