Beginning MDX

I'm pretty new at Mdx , so bare with me. I want to make a calculated member called Occupant Fatalities.

The occupant fatalities looks like this right now:

( [Measures].[Fatalities] , [Crash Person].[Person Type].&[Driver])

Now the question is , How do i add another person type to this calculated member. It wont work when i do this.

( [Measures].[Fatalities] , [Crash Person].[Person Type].&[Driver].&[Passenger])

whats the code for having more then one member from a hierachy? or having 2 members from different hierarchies?

any help is appreciated.

Well, one of my co-workers helped with this one. It was right there, and i didnt even know.

I guess you have to put each individual attribute with the measure. Looks like this.

([Crash Person].[Person Type].&[Driver], [Measures].[Fatalities])

+ ([Crash Person].[Person Type].&[Passenger],[Measures].[Fatalities] )

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.