Beginer question - calculated aggregation

Hi,

I have two measures: quantity and price.

The default aggregation sum is not valid for price, because I dont want sum(price), what I want is somethin like:

sum(quantity*price)/sum(quantity),

in other words, a weighted mean.

How can I do that?

I think that I have to place some code in the CALCULATE section, but I don't know exactly how.

Thanks.

have you tried placing it in your query? I tried it (using Northwind) and it runs just fine...

SELECT OrderId, SUM(UnitPrice*Quantity)/SUM(Quantity) FROM [Order Details] od

HTH,|||

CryptoKnight wrote:

have you tried placing it in your query? I tried it (using Northwind) and it runs just fine...

SELECT OrderId, SUM(UnitPrice*Quantity)/SUM(Quantity) FROM [Order Details] od

HTH,

That works for leaf data, but the problem is aggregations.

By default, Analysis Services 2005, makes sum() for calculate the aggregations.

|||

You have a few options. Probably the easiest thing to do is to hide your Price measure and create a calculated measure to display instead. This could be defined as

IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.PRICE/MEASURES.QUANTITY)

There are ways to overwrite the values of the original Price measure too, which I can go into if you're interested, but they're a bit more complicated.

Chris

|||

Chris Webb wrote:

You have a few options. Probably the easiest thing to do is to hide your Price measure and create a calculated measure to display instead. This could be defined as

IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.PRICE/MEASURES.QUANTITY)

There are ways to overwrite the values of the original Price measure too, which I can go into if you're interested, but they're a bit more complicated.

Chris

Ok, that seems to work fine.

Only I changed IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.TotalAmount/MEASURES.QUANTITY)

and I removed the old Price measure, because I dont need it anymore.

THANKS.