
1. Re: Total is not Suming correctly? Confused.
Jim Dehner Mar 2, 2017 2:28 PM (in response to taylor.johnson)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 Suming correctly? Confused.
Christina Gremore Mar 2, 2017 8:56 PM (in response to taylor.johnson)1 of 1 people found this helpfulHi Taylor,
The reason this is happening is because you wrote your 'W Coupon' calc as a preaggregated 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 rowlevel 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

3. Re: Total is not Suming correctly? Confused.
taylor.johnson Mar 15, 2017 11:02 AM (in response to Christina Gremore)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!

Test Workbook.twbx 98.2 KB


4. Re: Total is not Suming correctly? Confused.
Kaz Shakir Mar 15, 2017 12:16 PM (in response to taylor.johnson)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 Suming correctly? Confused.
taylor.johnson Mar 16, 2017 9:45 AM (in response to Kaz Shakir)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.