5 Replies Latest reply on Sep 28, 2016 2:38 PM by David Li

# Creating a total of blended portfolio

I am at my wits end and I could use some guidance.

I have dashboard that I’m working on with a number of
parameters. Here’s what I’m trying to accomplish:

I have a data set with a number of portfolios and month end
equity values that I’m trying to blend around different weightings/percentages.

I have a data set with a [portfolio name] and a [month end equity]
column. I have 5 unique parameters titled Portfolio1, Portfolio2, etc. and I have
5 unique parameters title Portfolio1 Amount, Portfolio2 Amount,
etc.

I have a calculated field titled “BlendChange” that
calculates the change of the month end equity from a reference date using the
following code:

IF ATTR([Portfolio Name]) = [Portfolio1] THEN (((Sum([Month
End Equity]) - [Close on Reference Date])/[Close on Reference Date])*
[Portfolio1 Amount])

ELSEIF ATTR([Portfolio Name]) = [Portfolio2] THEN
(((Sum([Month End Equity]) - [Close on Reference Date])/[Close on Reference
Date])* [Portfolio2 Amount])

ELSEIF ATTR([Portfolio Name]) = [Portfolio3] THEN
(((Sum([Month End Equity]) - [Close on Reference Date])/[Close on Reference
Date])* [Portfolio3 Amount])

ELSEIF ATTR([Portfolio Name]) = [Portfolio4] THEN
(((Sum([Month End Equity]) - [Close on Reference Date])/[Close on Reference
Date])* [Portfolio4 Amount])

ELSEIF ATTR([Portfolio Name]) = [Portfolio5] THEN
(((Sum([Month End Equity]) - [Close on Reference Date])/[Close on Reference
Date])* [Portfolio5 Amount])

ELSE Null

End

The chart displays the blend change as a stacked area chart
and correctly shows how that portion would have grown over time.

Here’s what I don’t have. I don’t have a total of the “BlendChange”
at each point because I get the error:

The input to TOTAL() cannot include a Table Calculation
function.

• ###### 1. Re: Creating a total of blended portfolio

Hi Mitch! This is a bit hard to figure out without a packaged workbook. Do you have one you could share?

Off the top of my head, I'd suggest that you try replacing TOTAL([BlendChange]) with WINDOW_SUM([BlendChange]) and see if that works.

• ###### 2. Re: Creating a total of blended portfolio

Here's an attachment of what I'm trying to accomplish. The Window_Sum at least didn't produce an error which I'll take as progress but it didn't fully resolve the issue. The Blend Sheet tab shows the correctly stacked view of the portfolio change but still without a total for the tooltip.

Thanks!

• ###### 3. Re: Creating a total of blended portfolio

Hi Mitch! I took the liberty of changing a bunch of things in this workbook to demonstrate a way you can do a lot of this much more easily. Firstly, instead of having a bunch of ATTR() in IF...ELSE statements, you can push that switching into a CASE...WHEN statement at the row level, like this (which we'll call [PF Amount Switch*]:

CASE [Portfolio Name]

WHEN [Portfolio1] THEN [Portfolio1 Amount]

WHEN [Portfolio2] THEN [Portfolio2 Amount]

WHEN [Portfolio3] THEN [Portfolio3 Amount]

WHEN [Portfolio4] THEN [Portfolio4 Amount]

WHEN [Portfolio5] THEN [Portfolio5 Amount]

ELSE NULL

END

You can then use this calculated field to power all your other calculations without the need to propagate the switching into them. For instance, here's the new BlendChange:

(SUM([Month End Equity]) - [Close on Reference Date])/[Close on Reference Date] * SUM([PF Amount Switch*])

Similarly, Portion simplifies to this:

[PF Amount Switch*]/100

The last step (to get the total) is using this calculation, but making sure that the underlying [Close on Reference Date] computes on Table Across while this table calculation goes along Portfolio.

WINDOW_SUM([BlendChange*])

In the attached workbook, note that I put an asterisk after the calculated fields I changed.

• ###### 4. Re: Creating a total of blended portfolio

Awesome Work David! Thanks, you streamlined it and everything looks great. I can't tell you how much I appreciate your help with this.

• ###### 5. Re: Creating a total of blended portfolio

You're very welcome! If you wouldn't mind, could you please mark the answer as correct so this no longer shows as unanswered? Thanks!