Average of x% top items in multidimensional table

[fosoto]

I have a dataset with multiple dimensions (month, hidro, comb, company) and i need to get the average of the x% lowest values of each year. 

I tried creating an aggregate table and taking the average of all values lower than the value  which corresponds to the x% using the Fractile() function. This works very well if i select only 1 year, but when i select multiple years the formula does not work (i think its because when selecting more years the fractile function takes the percentile of the whole selection rather than the percentile of the data of the corresponding year)

 

Works since Average _Low5% is as expected

Does not work when selecting more years (Average_Low5% changed for year 2019 and is not correct for the other years)

Is there a better approach to this?

Thanks!

[sunny_talwar · Accepted solution]

Try this expression

=Avg(Aggr(
   If(Sum(Income) <= Fractile(TOTAL <$(Res)> Aggr(Sum(Income),Comb,Hidro,$(Res)),0.05),
      Sum(Income)
   )
, Comb, Hidro, $(Res)))

[fosoto]

Hi Sunny, 

Worked perfect, thanks!