Filter out 10% of dimension values around min/max KPI-values

[did]

Dear Community-Members,

I have the following table (in red are min & max values in respective year):

What whould be the KPI-Formula to show the same without 10% of regions having values equal or close to min/max values in each year (i.e. to show those 80% in between)?

Data set and app are attached here.

Any help appreciated.

Thank you

[Kushal_Chawda]

@did  what is the expected output based on dummy data?

[did]

@Kushal_Chawda 

Initially the range boundaries (values between min and max) in each year are as follows:

According to the posed requirement (show no those 10% of regions having values equal or close to min/max values in each year,  i.e. to show those 80% in between), if we take 3 (10% out of 30) min values (light blue cells,) and 3 (10% out of 30) max values (dark blue cells) we will have another table with different  range boundaries:

This is exactly what is needed.

Further it should be possible also to build the range chart without regions but having exactly these new range boundaries, something like this:

[Kushal_Chawda]

@did  Got it. Let me give it a try

[Kushal_Chawda]

@did  Idea is have below expression for excluding 10% of min and max values 

if(sum(KPI)<> min(total <Year> KPI) and
sum(KPI)<> min(total <Year> KPI,2) and
sum(KPI)<> min(total <Year> KPI,3) and
sum(KPI)<> max(total <Year> KPI) and
sum(KPI)<> max(total <Year> KPI,2) and
sum(KPI)<> max(total <Year> KPI,3), sum(KPI))

But, it should be dynamic based on Number of regions. You can follow below approach

Create two variables on frond end which will produce 10% count of region and above expression dynamically. You can create this expression dynamically in script as well

vCount10%_Region = 
Count(distinct Region)*0.1

vKPIFormula=
'If(' & 

concat(
'sum(KPI)<>min(total <Year> KPI,'& ValueLoop(1,$(vCount10%_Region)) &') and ',chr(10))

& chr(10) &

concat(
'sum(KPI)<>max(total <Year> KPI,'& ValueLoop(1,$(vCount10%_Region)) &')' & 
if(ValueLoop(1,$(vCount10%_Region))='$(vCount10%_Region)' ,', sum(KPI)',' and'),chr(10))

&')'

Create a chart with Dimension Region and Year. and measure $(vKPIFormula)

[did]

@Kushal_Chawda very interesting approach (in script). Thank you very much!