# How to use multi level LAC in Quicksight calculated filed and apply it on aggregated value

**URL:** <https://community.amazonquick.com/t/how-to-use-multi-level-lac-in-quicksight-calculated-filed-and-apply-it-on-aggregated-value/44241>\
**Category:** Q&A\
**Tags:** admin, quick-sight\
**Created:** [March 27, 2025, 3:05pm UTC](https://community.amazonquick.com/t/how-to-use-multi-level-lac-in-quicksight-calculated-filed-and-apply-it-on-aggregated-value/44241 "2025-03-27T15:05:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![nidhi3](https://avatars.discourse-cdn.com/v4/letter/n/8edcca/32.png) [@nidhi3](https://community.amazonquick.com/u/nidhi3)\
**Post date:** [March 27, 2025, 3:05pm UTC](https://community.amazonquick.com/t/how-to-use-multi-level-lac-in-quicksight-calculated-filed-and-apply-it-on-aggregated-value/44241/1 "2025-03-27T15:05:59Z")

</div>

Consider the following data:

 ![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/4/b/4bfa682037df04c174449b9e8a67cce3d9d05ef5.png)

I have a dataset that includes `city`, `store`, `date`, and `product_id`. I created a calculated field called `Available_product`, which is defined as: distinct\_count({product\_id})

Now, I need to compute the average of available products at different levels:

1. **First Level** : Calculate the distinct count of `product_id` at the `city`, `store`, and `date` level.
2. **Second Level** : Compute the **average of the above distinct count** at the `city` and `date` level.
3. **Third Level** : Compute the **average of the second level values** at the `city` level.

I want to display this in a **table or pivot table visualization** in Quick Sight which would have city and **average available products**.

**Question:**

Is there a way to create a calculated field that achieves this aggregation hierarchy in Quick Sight? What would be the best approach to implement this?

---

<div class="post-metadata">

**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\
**Post date:** [March 28, 2025, 6:46pm UTC](https://community.amazonquick.com/t/how-to-use-multi-level-lac-in-quicksight-calculated-filed-and-apply-it-on-aggregated-value/44241/2 "2025-03-28T18:46:13Z")

</div>

Hello @nidhi3 , welcome to the Quick Sight community!

I would recommend checking out LAC functions:

> **[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/business-intelligence/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...

> [@Level-Aware Calculations: Tips and Best Practices](https://community.amazonquick.com/t/level-aware-calculations-tips-and-best-practices/17936):
>
> Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to pe…