# Level Aware Aggregate Calculation **URL:** \ **Category:** Q&A\ **Tags:** calculations, quick-sight\ **Created:** [July 26, 2022, 11:44am UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952 "2022-07-26T11:44:32Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Tanisha\_Shetty](https://avatars.discourse-cdn.com/v4/letter/t/e36b37/32.png) [@Tanisha\_Shetty](https://community.amazonquick.com/u/Tanisha_Shetty)\ **Post date:** [July 26, 2022, 11:44am UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/1 "2022-07-26T11:44:33Z")
Hi All, Can anyone help me recalculate the total units that replaces Internal Total Units - that remains constant for a chosen Period that reflects in the column Granularity and across all L1 deepdive categories? In the picture below, I’d want the Internal Total units to reflect the same number across 1 and 2 for Apr 1, 2020 and the same number for Jul 1, 2020 and so on and so forth… ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/3/3eaf01e7a9eae7e8e20bd83e446606be6def92c0.png) Please tell me if there is an easy way to create a calculated field that will help me. Thanks Tanisha ---
**Author:** ![robdhondt](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robdhondt/32/194_2.png) [@robdhondt](https://community.amazonquick.com/u/robdhondt)\ **Post date:** [July 27, 2022, 5:21pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/2 "2022-07-27T17:21:30Z")
Hi @Tanisha_Shetty - You can solve this by using a [Level Aware Calculation](https://docs.aws.amazon.com/quicksight/latest/user/level-aware-calculations.html) If you don’t need subtotals/totals the easiest approach would be to use a LAC-A. If you need subtotals/totals then you’ll want to use a LAC-W. It wasn’t clear to me how you’d wanted to handle the aggregation. Here is an example of a sum across all categories. ```auto sumOver({Total Units},[Dt], PRE_AGG) ``` ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/f/f84bf2c71cd23094239f04e7e41f5487a659fc5e.png) ---
**Author:** ![Tanisha\_Shetty](https://avatars.discourse-cdn.com/v4/letter/t/e36b37/32.png) [@Tanisha\_Shetty](https://community.amazonquick.com/u/Tanisha_Shetty)\ **Post date:** [July 27, 2022, 5:45pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/3 "2022-07-27T17:45:35Z")
Thanks for the reply Rob. This is exactly what I’m trying to achieve but my Total Units here is a calculated field with an if condition and my date field also appears as Date/ Week/Month/Quarter/Year basis user input. I tried to use the sumover function but it gives me an error. ![image](https://d1wl4jav5dp96l.cloudfront.net/original/2X/e/ec7bbec04169ec24682329e99e20b709279d96d7.png) Would you know how I can deal with it in this case? Many Thanks! Tanisha ---
**Author:** ![robdhondt](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robdhondt/32/194_2.png) [@robdhondt](https://community.amazonquick.com/u/robdhondt)\ **Post date:** [July 27, 2022, 5:55pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/4 "2022-07-27T17:55:56Z")
Hi @Tanisha_Shetty - Can you show the expression for {ChosenTotalUnits} ? It’s most likely an aggregate field and you’ll have to use an additional LAC-W. Similar to this post: [Trouble With "Custom" Calculated Field Type Within Analysis - #3 by connoranderson](https://community.amazonquick.com/t/trouble-with-custom-calculated-field-type-within-analysis/4800/3) ---
**Author:** ![Tanisha\_Shetty](https://avatars.discourse-cdn.com/v4/letter/t/e36b37/32.png) [@Tanisha\_Shetty](https://community.amazonquick.com/u/Tanisha_Shetty)\ **Post date:** [July 29, 2022, 9:51am UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/5 "2022-07-29T09:51:34Z")
> [@robdhondt](#): > > senTotalUnits} ? It Hi ROb, Please find the formula for Chosen Total Units - ifelse(${ChosenType}=‘Internal’,{Internal\_total\_units},{External\_total\_units}) This is dependant on the parameter driven field ChosenType. Thanks Tanisha ---
**Author:** ![robdhondt](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robdhondt/32/194_2.png) [@robdhondt](https://community.amazonquick.com/u/robdhondt)\ **Post date:** [July 29, 2022, 2:06pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/6 "2022-07-29T14:06:36Z")
{Internal\_total\_units} & {External\_total\_units} are calculated fields with aggregation functions right? They have **sum()** functions in the expression? You can remove the aggregation and just use the columns. The sumOver will perform the sum aggregation. ```auto ifelse(${ChosenType}=‘Internal’,{Internal_units},{External_units}) ``` ---
**Author:** ![Tanisha\_Shetty](https://avatars.discourse-cdn.com/v4/letter/t/e36b37/32.png) [@Tanisha\_Shetty](https://community.amazonquick.com/u/Tanisha_Shetty)\ **Post date:** [July 29, 2022, 6:29pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/7 "2022-07-29T18:29:15Z")
Hi Rob, I am calculating the Internal\_total\_units via a sumif function: sumIf({total\_units},type = ‘Internal’). Do you think I should change it to an if condition and calculate it without the aggregation? Thanks Tanisha ---
**Author:** ![robdhondt](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/robdhondt/32/194_2.png) [@robdhondt](https://community.amazonquick.com/u/robdhondt)\ **Post date:** [July 29, 2022, 6:32pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/8 "2022-07-29T18:32:55Z")
You have it right. If you do that your sumOver should work. ---
**Author:** ![Tanisha\_Shetty](https://avatars.discourse-cdn.com/v4/letter/t/e36b37/32.png) [@Tanisha\_Shetty](https://community.amazonquick.com/u/Tanisha_Shetty)\ **Post date:** [July 29, 2022, 7:26pm UTC](https://community.amazonquick.com/t/level-aware-aggregate-calculation/4952/9 "2022-07-29T19:26:38Z")
> [@robdhondt](#): > > `sumOver({Total Units},[Dt], PRE_AGG)` Thanks Rob! This worked perfectly! 🙂