Apply filter to exclude certain rows from PERCENTILEX calculation

[BUserTG]

I have a sales dataset and am trying to use the PERCENTILEX.INC function to identify certain percentiles (25% and 75% quartiles specifically). Due to some outliers and noise in the data, I want to exclude certain records. What I'm wanting to do is apply a filter, say exclude groupings where [Sales] < 1000, and then apply the percentile function on the remaining dataset. This will ensure the percentile quantification is only focusing on data that I want it to. One challenge is the filter needs to be applied on an aggregation, meaning the base data needs to be aggregated up to a column and then filtering needs to be applied.

For example,

Item	Sales	Week
A	10	1
A	20	2
B	50	2

The percentile (and filtering of data) needs to be applied on:

Item	Sales
A	30
B	50

I've tried numerous iterations to created a summarized table, etc. Below is my lastest:

VAR AggTable =

SUMMARIZE(FactTable, Fact[Item], "AggValue", CALCULATE([Sales], FILTER(FactTable, [Sales] > 1000)))

RETURN

CALCULATE(PERCENTILEX.INC(AggTable, [AggValue], 0.25), ALLEXCEPT(Item, [Category]))

Thanks.

[amitchandak]

@BUserTG , if you want to filter sales after aggregation

VAR AggTable =

FILTER(SUMMARIZE(FactTable, Fact[Item], "AggValue", [Sales]), [Sales] > 1000)

RETURN

CALCULATE(PERCENTILEX.INC(AggTable, [AggValue], 0.25), ALLEXCEPT(Item, [Category]))

[BUserTG]

Not sure that will apply the same context as the ALLEXCEPT() that is needed to ensure the summarized table does not get filtered down. The calculation you wrote will end up applying filter context on each row.

[Kaii]

have you figure out yet? I am facing the same issue