Sum of products (multiplication) in MDX

Question

I have an MDX query like this one:

with 
member [Measures].[Currency] as [Currency].[Code].Currentmember.Member_Key
member [Measures].[Date] as [Time].[Date].Currentmember.Member_Key
member [Measures].[Wholesale Price Totals] as [Measures].[Wholesale Price] * [Measures].[Net Sold Units]
select
{
[Measures].[Currency]
,[Measures].[Date]
,[Measures].[Sold Units]
,[Measures].[Wholesale Price Totals]
}
on columns,
ORDER
(
    NONEMPTY
    (
        CROSSJOIN
        (
            {[Time].[Date].&[2013-02-26T00:00:00],[Time].[Date].&[2013-02-27T00:00:00]}
            ,EXCEPT([Currency].[Code].Members, [Currency].[Code].[All])
        ), 
        [Measures].[Sold Units]
    ), 
    [Measures].[Date], BASC
)
on rows
from MyCube

I need to have as [Wholesale Price Totals] the sum of all the [Sold Units] (is a quantity) * [Wholesale Price]. The query like this retrieves the sum of all the Sold Units multiplyied by the sum of the Wolesale prices, I need to have the sum of every sold units "cell" multiplyied by their wholesale price.

I'm trying to do that since some days ago and this is driving me nuts!

Thanks in advance!

Answer 1

Multiplications needs to be done at the leaf level which makes it considerably slower than regular measures. Ideally you should consider multiplication at the ETL stage. If it is not possible, you can use the methods described in the following post http://sqlblog.com/blogs/mosha/archive/2005/02/13/performance-of-aggregating-data-from-lower-levels-in-mdx.aspx