Forum Discussion
Apply filter to exclude certain rows from PERCENTILEX calculation
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.
3 Replies
- amitchandak
Super User
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]))