5 Replies Latest reply on Mar 16, 2017 9:45 AM by taylor.johnson

    Total is not Sum-ing correctly? Confused.

    taylor.johnson

      Hello,


      I am trying to determine a weighted average coupon which is simply (Property A record):

       

      Loan Balance ($24MM) / Total Loan Balance ($89.79MM) * Coupon (1.95%) = 0.52%

       

      All of these calculations calculate corrected ('W Coupon along Table (Down)), however it SUMS up incorrectly on the 'Total' line. The overall sum should 3.10% but for some reason it is 2.07%.

       

      My formula for the weighted coupon is: SUM([Loan Balance]) / WINDOW_SUM(SUM([Loan Balance]))*AVG([Coupon])

       

      Please help if you can, twbx attached.

       

      Thanks,

       

      Taylor

        • 1. Re: Total is not Sum-ing correctly? Confused.
          Jim Dehner

          Hi taylor

          Looks pretty complex - just a question here may not help -

          I looked at your data and most of your coupons have a value like .0xx something but there are 4 values at 2.38

           

          Just looks strange

           

          Jim

          • 2. Re: Total is not Sum-ing correctly? Confused.
            Christina Gremore

            Hi Taylor,

             

            The reason this is happening is because you wrote your 'W Coupon' calc as a pre-aggregated calculation, which means that the Grand Total won't just add up all the percentages in the column. It's actually performing the calculation itself for all the values at the proper aggregation. Notice that your 'W Coupon' pill appears on the viz as AGG(W Coupon), not SUM(W Coupon). So what the Total is calculating is this:

             

            SUM(Loan Balance) ($89.79MM) / WINDOW_SUM(SUM(Loan Balance)) ($89.79MM) * AVG(Coupon) (1.95+3.35+3.75+3.35+0+0/6)

             

            Because the first part of the equation comes out to 1, it's basically just returning you the average of all your coupon values (2.07)

             

            If you want it to return the sum of the percentages, you need to write this as a row-level calculation, so that you can put it on the viz with a SUM aggregation, and then the Total line can SUM all the values of the output. You can do that with LODs, like so:

             

            [Loan Balance] / { FIXED [Property Type] : SUM([Loan Balance]) } * Coupon

             

            Now the one issue with this is that you've excluded Property 29 from your viz. However, an LOD will want to take the Loan Balance value of Property 29 into account. So what you have to do is add your Property Name filter to context. Once you do that, the calculation I provided above will return 3/10% in the totals line.

             

            If this answer resolves your issue, please mark your question as answered.

             

            -Christina

            1 of 1 people found this helpful
            • 3. Re: Total is not Sum-ing correctly? Confused.
              taylor.johnson

              Christina,

               

              I am also having the same issue in the attached twbx.  However, the filters are a little different so using { FIXED [Property Type] did not work.  Since I was filtering by Fund, I tried { FIXED [Fund] but that did not work.

               

              Thanks! 

              • 4. Re: Total is not Sum-ing correctly? Confused.
                Kaz Shakir

                Taylor,

                 

                Would you be able to accomplish what you want, if you change the calculation in your "Weighted Coupon" calculated field to the following:

                 

                Weighted Coupon

                [Coupon]*

                ([Loan Amt (MM)]/

                    {EXCLUDE [Property],[Term],[Coupon],[Maturity Date]:

                        SUM([Loan Amt (MM)])})

                 

                Please let us know if that works.

                Kaz.

                • 5. Re: Total is not Sum-ing correctly? Confused.
                  taylor.johnson

                  That did not work, that calculation basically returned the exact same number that was in the original coupon field.  You can try it in the previous TWBX.