tomigisi


I am now compute the measures by multiplying two measures.

And the time dimension is Year-Month.

For Example :

Data1 Data2
Jan: 1 5
Feb: 2 6

Mar: 3 7

Apr: 4

Month Total is 1*5+ 2*6 + 3*7 = 38
Year Total is (1+2+3+4)*(5+6+7) = 180

The month total is correct ,but the year is not correct.

I have read many article about the multiply problem.
But there is no way to resolve it.

Thanks in advance,

tomogisi




Re: The total is not correct by multiplying measures.

Darren Gosbell


I am assuming that you are saying that you want the Data1 * Data2 to be done at the leaf level and then added up. There are a couple of ways of doing this that come to mind.

1. If data1 and data2 are in different measure groups, you can use a measure expression. These are always done at the leaf level and you should be able to find out information about these in Books Online.

2. You can force the calculation at the month level by taking the descendants of the current member and summing the multiplication of the 2 measures

eg.

SUM(Descendants([Date].<hierarchy>.CurrentMember, [Date].<hierarchy>.[Month]), Measures.data1 * Measures.Data2)







Re: The total is not correct by multiplying measures.

tomigisi

Dear Darren Gosbell:

Thank you for your reply.

My problem is belong to the 2.

And it does the work.

But the total is left blank.

How can I show the total.

Thanks again.

tomogisi.






Re: The total is not correct by multiplying measures.

Russ6578

Tomogisi,

Did you ever resolve this calculation problem. I have been unable to come up with a solution

Thanks

Russ





Re: The total is not correct by multiplying measures.

tomigisi

Dear Russ

I Have resolved it pefectly.

what is the problem with you

waiting for..





Re: The total is not correct by multiplying measures.

Russ6578

I am rather new at mdx here is my example

Product Units Unit Price Calc Meaure Result Desired Result
f1 1 200 200 200
f2 1 100 100 100
f3 1 300 300 300
f4 1 0 0 0
f5 0 400 0 0
total 4 1000 4000 600


The calculated measure currently is simply (units * unit price) - the total values in the matrix value and statistic columns are multiplying for this calculation. I simply want to multiply the leaf values of the product dimension, then sum the result over the product hierarchy. My total in the desired result column should be 600, but the 4000 is what the calculated measure is returning.

Does anyone have an example of you mdx code that multiplies two measures at the leaf level only then aggregates the results over the hierarchy Any help you could lend would be greatly appreciated

Russ




Re: The total is not correct by multiplying measures.

Darren Gosbell

This is a typical issue with Summing then Multiplying not being the same as Multiplying then Summing. There are a couple of approaches to correct this.

If the measures are in the same fact table, then the best thing to do is to create a calculated column in the DSV that mulitplies the two columns and then base the measure off that.

If the two measures are in separate fact tables then you should create a new measure and put the multiplication in the measure expression.

Finally you could calculate this in MDX, although it would probably be slower than either of the other options. I'm guessing from your example that you might be dealing with a parent-child hierarchy. The general pattern would be something like:

SUM(Descendants( [Product].[<hierarchy>].CurrentMember,,LEAVES), Measures.Units * Measures.[Unit Price])






Re: The total is not correct by multiplying measures.

Russ6578

Thank you for responding - you are correct I am using a parent child hierarchy. The measures are coming from two different fact tables, so I cannot take care of this on the relational side. Right now I have two measures and have created a calculated measure multiplying them together. If I am understanding you correctly, I should create a measure that pulls from the two different fact tables and multiply them at that point.

I will also try the MDX code.

Thank you - I'll let you know how I make out

Russ





Re: The total is not correct by multiplying measures.

Russ6578

I got it to work thank you for your help

Russ