I'm trying to use Analysis Server to perform a portfolio analysis and
am have trouble with aggregation at different levels. Here is a
simple example of what I'm looking for.

I have a portfolio accounts worth $10000. Each month I recieve
payments on the accounts which I would like to show as a percentage
over the original portfolio balance of 10,000. I want to use
portfolio and month as dimension and Return% as the fact. My fact
table currently looks like:

Portfolio Month Payments PortfolioBalance
1 200301 100 10000
1 200302 300 10000
....

I want to be able to display output like this
Month
Portfolio 1 2 Total
1 1% 3% 4% (Total return = 400/10000)

Since the fact table contains measures at two different levels I get
the wrong answer for the total (400/20000 = 2%) but the right answer
at each month. I thought of spliting the portfolio balance among each
month payments where received but then my total is right and
individual months are wrong. My only other thought is to create
measure for each month going out a max of 36 months but this defeats
the purpose of ad hoc analysis.

Has anyone solved a similar problem? I would think this is a fairly
common financial analysis problem. Thanks for your help.

Dave

I presume the aggregation function assigned to the PortfolioBalance measure
is SUM...? (It's the default function.) You need to implement this as a
semi-additive measure (additive over all dims except time). This entails
creating a calculated member and hiding PortfolioBalance. To see syntax,
search this group for "semi-additive" or the like.

public @ the domain below
www.tomchester.net

"Dave" <davidbr93 (AT) hotmail (DOT) com> wrote

You could have different aggregations for Payments and
PortofolioBalance measures. Leave the default 'Sum'
aggregation for Payments and change it to 'Max' or 'Min'
for PortofolioBalance (since you have 10000 for every
month, the aggregated value will be 10000 as well). Be
careful about the 'Solving Order' though. If the 'Solving
Order' is wrong you may have the wrong answer for the
total ('Max' of monthly answers instead of 'Sum')