# Calculated field grouping

**URL:** <https://community.amazonquick.com/t/calculated-field-grouping/12065>\
**Category:** Q&A\
**Tags:** calculations, quick-sight\
**Created:** [April 10, 2023, 9:58pm UTC](https://community.amazonquick.com/t/calculated-field-grouping/12065 "2023-04-10T21:58:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![paof25](https://avatars.discourse-cdn.com/v4/letter/p/82dd89/32.png) [@paof25](https://community.amazonquick.com/u/paof25)\
**Post date:** [April 10, 2023, 9:58pm UTC](https://community.amazonquick.com/t/calculated-field-grouping/12065/1 "2023-04-10T21:58:43Z")

</div>

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%

 ![IMG_0286](https://d1wl4jav5dp96l.cloudfront.net/original/2X/b/b5998b7566214871a497a1157ac0570d3f7b7426.png)

Calculated field I have been using is sum(payment)/sum(loan\_balance)  
Does Anyone know how can I fix this?

---

<div class="post-metadata">

**Author:** ![apjvinod](https://avatars.discourse-cdn.com/v4/letter/a/8baadc/32.png) [@apjvinod](https://community.amazonquick.com/u/apjvinod)\
**Post date:** [April 11, 2023, 1:30am UTC](https://community.amazonquick.com/t/calculated-field-grouping/12065/2 "2023-04-11T01:30:01Z")

</div>

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 .

> **[Using level-aware calculations in Amazon QuickSight - Amazon QuickSight](https://docs.aws.amazon.com/quicksight/latest/user/level-aware-calculations.html)**
>
> Use level-aware calculations in an Amazon QuickSight analysis to insert a window or aggregate function into the order of evaluation.

> **[Create advanced insights using level-aware calculations in Amazon QuickSight...](https://aws.amazon.com/blogs/big-data/create-advanced-insights-using-level-aware-calculations-in-amazon-quicksight/)**
>
> Calculation at the right granularity always needs to be handled carefully when performing data analytics. Especially when data is generated through joining across multiple tables, the denormalization of datasets can add a lot of complications to make...

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

Thanks  
Vinod