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.
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.
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]
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.
In the attached workbook, note that I put an asterisk after the calculated fields I changed.
Awesome Work David! Thanks, you streamlined it and everything looks great. I can't tell you how much I appreciate your help with this.
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!