Hi Guys,
Im designing a writeback cube in AS to assist in our prodcution planning
function which is primarily to show the effect on the monthly build plan
value when quantities of products in the plan are ammended.

I have two measures in my fact table Qty and Cost

I have a Calculated Member in the Cube - Value = (Qty * Cost)

I have a Product Dimension with 4 levels :

Product Type
Product Group
Product Subgroup
Product ID.

The general idea is the a user changes the Qty field which in turn
influences the Calculated Member Value

At the lowest leaf level of the Product Dimension the Calculated Member is
correct, However, when we drill up to the next level it is not,

eg :

ProductID Qty Cost Value
Item1 1 200 200
Item2 10 100 1000
Item3 10 50 500

When we drill up we get not the total of the Values ie 1700, but the total
Qty * total Cost, ie 21 * 350 and this continues up through the Product
dimension.

There must be a way around this, ... is there ?

Many Thanks in Advance,
Steve.

Cost is non-additive measure and your value must be calculated at only leaf
level before aggregation. You can try next expression but you may run into
performance issue if you have many dimensions to be considered. My example
considers only product dimension.

Value:
IIF(IsLeaf(Product.CurrentMember), Qty * Cost,
SUM(Product.CurrentMember.Children, Value))

or

SUM(Descendants(Product.CurrentMember, [Product ID]), Qty * Cost)

Ohjoo Kwon
www.olapforum.com


"Skull Monkey" <skull (AT) skullmonkey (DOT) co.uk> wrote

Many Thanks Ohjoo, .. that works just fine.

Regards,
Steve.

"Ohjoo Kwon" wrote: