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

    Creating a total of blended portfolio

    Mitch Wood

      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
          David Li

          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
            Mitch Wood

            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
              David Li

              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
                Mitch Wood

                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
                  David Li

                  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!