## 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.

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.

WITH
MEMBER Measures.QtyPerOrder as measures.[reseller Order Count]
/ measures.[Reseller Order Quantity]

EXP(
SUM(
Descendants([Date].Calendar.CurrentMember
,[Date].[Calendar].[Month])
,LN(1+Measures.QtyPerOrder)
)
) - 1

SELECT
{[Measures].[Reseller Order Count]
,[Measures].[Reseller Order Quantity]
,[Measures].[QtyPerOrder]
ON COLUMNS,

Descendants([Date].[Calendar].[Calendar Year].&[2003]
,[Date].[Calendar].[Month],
SELF_AND_BEFORE) ON ROWS

Update 21 Aug 2006: There is now also a Stored Procedure that will do multiplication at https://asstoredprocedures.github.io/functions/Multiplication/. 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.```
```WITH MEMBER MEASURES.MULTIPLYDEMO
AS
ASSP.Multiply(
[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

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

### # re: Product of values in MDX

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

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

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