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.

Date
====
2005
  Q1
     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 http://www.russell.com/ca/Investor_Services/Personal_Rate_of_Return.asp

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.

Update 21 Aug 2006: There is now also a Stored Procedure that will do multiplication at http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. The stored procedure appears to offer a significant performance increase, so it would be well worth investigating.