Forum Discussion

BUserTG's avatar
BUserTG
Frequent Visitor
5 years ago

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,

ItemSalesWeek
A101
A202
B502

 

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

ItemSales
A30
B50

 

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

  • 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's avatar
      BUserTG
      Frequent Visitor

      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's avatar
        Kaii
        Icon for Helper I rankHelper I

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