Calculated field grouping

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?

Hi @paof25

Welcome to QuickSight Community .

In QuickSight with Level-aware calculations (LAC) you can specify the level of granularity that you want to compute window functions or aggregate functions.

In this case you might need to update the total balance amount calculated to

sum(initial loan balance,[ ] ) – overall loan balance after the filter
sumover(sum(initial loan balance),[ ], PRE_FILTER ) – overall loan balance before applying filter

Please refer the below documentation for additional details and sample calculation expressions .

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.

Thanks
Vinod

2 Likes