# Not able to use 2 level of aggregations with same set of grouping columns

**URL:** <https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552>\
**Category:** Q&A\
**Tags:** formulas, quick-sight\
**Created:** [July 10, 2023, 6:27pm UTC](https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552 "2023-07-10T18:27:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![PavanKanna](https://avatars.discourse-cdn.com/v4/letter/p/ea5d25/32.png) [@PavanKanna](https://community.amazonquick.com/u/PavanKanna)\
**Post date:** [July 10, 2023, 6:27pm UTC](https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552/1 "2023-07-10T18:27:29Z")

</div>

Hi All,

Is there way to achieve using 2 LAC functionality with other QS functions . I have a usecase to do sum(avg(col,grp by 1,2,3),grp by 1,2,3)

code :  
Sum(avg((ifelse(  
${Currency} = “EUR”,{eur\_rate},  
${Currency} = “USD”,{usd\_rate},  
${Currency} = “LocalMP”,1  
,0)  
\*  
ifelse(  
${BpsBY} = “PCcogs”,{p\_cogs\_cy},  
${BpsBY} = “PRev”,{p\_rev\_cy},  
${BpsBY} = “NetRev”,{net\_rev\_cy}  
,0)),[period,{gl\_product\_group},{week\_der},{brand\_code},{vendor\_code},{vendor\_company\_code}]),[period,{gl\_product\_group},{week\_der},{brand\_code},{vendor\_code},{vendor\_company\_code}])

It errored out  
Visual agg functions can’t be used operands of as Level Aware Calculation (LAC) agg functions

Thanks,  
Pavan

---

<div class="post-metadata">

**Author:** ![gillepa](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/gillepa/32/3891_2.png) [@gillepa](https://community.amazonquick.com/u/gillepa)\
**Post date:** [July 11, 2023, 12:33pm UTC](https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552/2 "2023-07-11T12:33:12Z")

</div>

Hi @PavanKanna

try to create the nested aggregation as a separate calculated field at the DATA SET PREP level, not in the analysis.

Hope it helps,  
GL

---

<div class="post-metadata">

**Author:** ![PavanKanna](https://avatars.discourse-cdn.com/v4/letter/p/ea5d25/32.png) [@PavanKanna](https://community.amazonquick.com/u/PavanKanna)\
**Post date:** [July 14, 2023, 2:23am UTC](https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552/3 "2023-07-14T02:23:33Z")

</div>

Thanks for responding i’m getting weird issue at dataset level not able to

avg(p\_rev\_cy,[{period}])

Function `AVG` should have 1 argument(s) instead of 2 argument(s).

---

<div class="post-metadata">

**Author:** ![gillepa](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/gillepa/32/3891_2.png) [@gillepa](https://community.amazonquick.com/u/gillepa)\
**Post date:** [July 14, 2023, 4:03pm UTC](https://community.amazonquick.com/t/not-able-to-use-2-level-of-aggregations-with-same-set-of-grouping-columns/15552/4 "2023-07-14T16:03:30Z")

</div>

try

avgOver( sum({p\_rev\_cy}), [{period}], PRE-AGG)

FYI:  
calculation level

```
(Optional) Specifies the calculation level to use:

    PRE_FILTER – Prefilter calculations are computed before the dataset filters.

    PRE_AGG – Preaggregate calculations are computed before applying aggregations and top and bottom N filters to the visuals.

    POST_AGG_FILTER – (Default) Table calculations are computed when the visuals display.

```