CASE
So I have been trying to create a payment rate curve for the yearly batches that get a loan. The payment rate is a ratio composed of sum(payments/sum(Initial loan balance). To create the payment curve we need an X-axis with the months since loan underwriting, in other words after the number of months since the loan underwriting the customer paid his debt part of his debt; and in the Y-axis we have the payment rate.
Example: if in 2019 we had a batch of accounts that have created a loan summing to a total balance of $1M, and these accounts paid $100k in the first month after the loan was underwritten, 10k in the second month, 200k in the third month… So as an output, we will have: that the batch of 2019 in the first month paid 10% ($100k/$1M), the second month 1% ($10k/$1M), the third month 20% ($200k/$1M)…
However, when I upload the data to Quicksight and create the calculated field it groups wrong. Basically, it takes the first month and instead of dividing it with the total loan balance ($1M) it only considers the account-level loan balance with increases a lot my payment rate. Please find below the way the graph is being computed, as you can see the grouping makes my payment rates to be way high. The sum of all rates should equal 100% if doing the running sum, in other words, the payment rate should be around 10% to 0%
Calculated field I have been using is sum(payment)/sum(loan_balance)
Does Anyone know how can I fix this?