Calculated field question sum of a product rather then the product of sums

Here is my calculated field. I want to sum the “effective duration” * “portfolio weight” but what I get back is the sum of the “effective duration” * sum of “portfolio weight”.

How would I write that code or should I do that calculation when I am importing the data into the fact table.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration]
AS Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Effective Duration]
)
Then Null
When IsEmpty
(
[Measures].[Portfolio Weight]
)
Then Null
Else (
// The Root function returns the (All) value for the target dimension.
[Measures].[Effective Duration] * [Measures].[Portfolio Weight]
)

End,
FORMAT_STRING = "#,#.0000",
VISIBLE = 1 ;

I believe the problem is caused by the fact that calculated measure does not have an aggregation function by itself.

At each level in a user hierarchy, it will have to compute its value based on the values of underlying regular measures you supplied in the expression. Hence, since in most likelihood 'effective duration' and 'portfolio weight' are aggregated using Sum function, this explains the result you are getting.

You may want to specify how to compute the calculated measure for each level of the 'target hierarchy'.

The following example does that using recursive definition for Weighted Duration. It will look at whether currentmember is a leaf in the 'target hierarchy' or not.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration] AS

IIF ( IsLeaf([Target Dim].[Target Hierarchy].CurrentMember),

[Measures].[Effective Duration] * [Measures].[Portfolio Weight]),

Sum(([Target Dim].[Target Hierarchy].CurrentMember.Children, [MEASURES].[Weighted Duration])))

|||

I think this is the right concept but I need to go all the way down to the fact table. Either that or I do it in the FactTable itself. I feel like that's somewhat of a shortcut. If I precalculate everything in the fact table during the data import I feel like I'm cheating a little. I thought olap was made for this sort of thing. Weightings/ratios are rather basic.

The other part about it is pulling out a measure for a specific level in the heirarchy. No matter what dimensions I am using, if I am using DimPortfolio, I want the total of a given measure (market value) for the "All" level of the portfolio. I'm having trouble with that too.

|||

Precalculating is the best solution, given some limitations that calculated members/measures have.

You can submit your wishlist to SQL Server group though, I believe I saw some website dedicated to that, https://connect.microsoft.com