Dear all,

Calculated Member, [Total Sold] = [Measures].[Quantity] * [Measures].[Unit
Price]

How to apply aggregation on the calculated member, [Total Sold],
1000+2000=3000, instead of 6000 ?

Quantity Unit Price Total Sold
Total Product 30 200 6000-->should be 3000
Product A 10 100 1000
Product B 20 100 2000


Thanks in advance,
Tan

you have to create an average unit price (UnitPriceSum / UnitPriceCount)
then you'll have the correct value.

or you have to create a new measure (not a calculated measure) which is the
Total Sold value (Qty * Price)

"cct" <peoplecosoftware (AT) newsgroup (DOT) nospam> wrote

You need to make the calculated member do the summation at a lower
level than it is currently. Therefore you'll need to force it down to
the lowest level of product to do the summation.

Give the calculation below a try...

SUM(DESCENDANTS([Product].currentmember,[product].[product lowest
level]),[Measures].[Quantity] * [Measures].[Unit Price])

Cheers,
L

cct wrote:

In article <uZ5ugAE#FHA.3680 (AT) TK2MSFTNGP09 (DOT) phx.gbl>,
willgart (AT) BBBhotmailAAA (DOT) com says...
Summing the average will not be accurate as the unit price varies

This option would perform best, the SUM(DESCENDANTS(...)) option posted
parallel to this is the MDX way of doing this, but does not perform well
on large dimensions.

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

More discussion about this subject here:
http://www.sqljunkies.com/WebLog/mos...2/13/7784.aspx

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Lozza" <laurence_hoff (AT) hotmail (DOT) com> wrote