I am multiplying one set of values against a corresponding set of other values and wish to sum the result of all the products. I am querying using MDX and when I get the results from the query and sum them in excel, they add up to the correct number.
However, when MDX sums them, the number is off.
The calculation works as follows:
I am multiplying a "Yearly Average Save Rate" percentage times a "Account Volume" percentage. Both of the percentages are subdivided into buckets with a couple dimensions. The multiplication is like the SUMPRODUCT function
in excel, the save rate percentage in bucket A is multiplied by the Account Volume percentage in bucket A, and so on.
If I look at the result of January 2018, I see the products of these multiplications are:
- 7.71%
- 17.88%
- 0.60%
- 22.22%
- 15.14%
- 0.47%
When I sum these in excel, I get 64.02%. When I use MDX, I get 63.83%. Here is my MDX so far for creating some of the calculated members:
WITH MEMBER [save rate times volume]
AS
(
[Measures].[Non-fixed Principal Save Rate] * [Measures].[Target Setting Account Volume]
),
FORMAT_STRING = "PERCENT"
MEMBER [Average yearly Save Rate]
AS
(
Avg(
([Measures].[Non-fixed Principal Save Rate],
[Date].[Calendar Month].[All])
)
)
MEMBER [volume times yearly save rate average]
AS
(
[Measures].[Target Setting Account Volume] * [Average yearly Save Rate]
)
[volume times yearly save rate average] is the calculated measure that provides the values in my example above. Anybody have any idea why MDX calculates the SUM of these percentages incorrectly? or any advice on how to ensure it calculates correctly?
