problem with nested aggregation

[beck_bakytbek]

Hi Folks,

i have a Question, my tables has 3 Values

Employee , ExValue

1,  11%

2,  37,9%

3,  22,3 %

for the Field: ExValue i used the following calculation:

(((sum({<Flag1 ={1}>}[VAL Sales])/(sum({<Flag2 ={1}>}Bugdet)-sum({<Flag3 ={1}>}Holiday))) / (PlanValue)) *100)

now my problem: 

i want to show the Avg of Values (11%, 37,9%, 22,3%) =23,73% , with above expression i have always the message: nested aggregation not allowed.

Does anybody know how to solve this issue?

Thanks a lot 

Beck

[kaanerisen · Accepted solution]

Hi Beck,

You can not use aggragate functions like avg(sum(...)). You should combine with aggr() function.

avg(aggr((((sum({<Flag1 ={1}>}[VAL Sales])/(sum({<Flag2 ={1}>}Bugdet)-sum({<Flag3 ={1}>}Holiday))) / (PlanValue)) *100),Employee))

[trdandamudi · Accepted solution]

Can you give a try on the below expression:

Avg(Aggr((((sum({<Flag1 ={1}>}[VAL Sales])/(sum({<Flag2 ={1}>}Bugdet)-sum({<Flag3 ={1}>}Holiday))) / (PlanValue)) *100),Employee))

[beck_bakytbek]

Hi Kaanerisen thanks a lot for your time and help

[beck_bakytbek]

Hi Trdandamudi,

thanks a lot for your help and time