Product of values in MDX

There was an interesting question on the Analysis Services newsgroup tonight which involved a requirement to produce a product of a set of values.

ie. value1 * value2 * value3 * .... valueN

You could do something like this with a unary operator, but that would change the aggregation for all measures.

In order to do this for just a single measure I proposed exploiting a technique I saw Itzik Ben-Gan use in T-SQL.

The basic mathematical proof for this technique was the following:

logN (val1*val2*...*valn) = logN(val1) + logN(val2) + ... + logN(valn)

logN (val1*val2*...*valn) = sum(logN(col_with_vals))

val1*val2*...*valn = EXP(sum(logN(col_with_vals)))

The question on the newsgroup related to trying to geometrically link quarter totals based on the following formula.

     Month 1 - 2%
     Month 2 - 3%
     Month 3 - 2.5%
  Q2 - 3%
  Q3 - 4%

The formula for Q1 would be (1+2%*1+3%*1.2.5%)-1. See

Below is a rough sample against the AS2k5 Adventure Works sample database. I did not have a rate measure handy so I fabricated one from the 2 reseller order measures. I have included all the raw figures in the query so that you can confirm the results for yourselves.

   MEMBER Measures.QtyPerOrder as measures.[reseller Order Count]
      / measures.[Reseller Order Quantity]
MEMBER Measures.GeometricLinkedTotal as
     ) - 1

   {[Measures].[Reseller Order Count]
   ,[Measures].[Reseller Order Quantity]

   Descendants([Date].[Calendar].[Calendar Year].&[2003]

FROM [Adventure Works];

Update 21 Aug 2006: There is now also a Stored Procedure that will do multiplication at The stored procedure appears to offer a significant performance increase, so it would be well worth investigating.

-- An example query showing how the Multiply() function can be used to 
-- multiply a measure value for the members of a set.
  [Date].[Day Name].[Day Name].MEMBERS.ITEM(0):[Date].[Day Name].CURRENTMEMBER
  , [Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],MEASURES.MULTIPLYDEMO} ON 0,
[Date].[Day Name].[Day Name].MEMBERS ON 1
FROM [Adventure Works]

Print | posted on Tuesday, July 18, 2006 9:30 PM

Comments on this post

# re: Product of values in MDX

Requesting Gravatar...
Great idea, I posted this to a newsgroup a while ago and never saw a response.
Left by Michael on Aug 21, 2006 8:22 PM

# re: Product of values in MDX

Requesting Gravatar...
Do you need to include anything in order to use LN() function ?
When i try to run your sample query on AS2K i get an error at the position where LN function is used. Any idea ?
Left by miner on Jun 25, 2008 8:35 PM

# re: Product of values in MDX

Requesting Gravatar...
AS2K would use slightly different libraries, the LN function might be called something different like LogN or seomthing. Unfortunately I don't have easy access to an AS2k box where I could check this for you.
Left by Darren Gosbell on Feb 04, 2009 12:09 PM

Your comment:

 (will show your gravatar)