How to Filter PERCENTILE.EXC to Remove Top & Lowest 5 % using DAX

[Anonymous]

Hi Team,

I created measure using the function PERCENTILE.EXC as show below.

SalesPercentile90 = PERCENTILE.EXC(Query[SalesAmount],.09)

However, how can I remove the Top 5% and Lowest 5% using the DAX.

The picture below show sample of data

Thank you in Advanced

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous,

Try this :

Filter =
VAR _min =
    PERCENTILE.EXC( 'Table'[Value], 0.05 )
VAR _max =
    PERCENTILE.EXC( 'Table'[Value], 0.95 )
RETURN
    FILTER( ALL( 'Table' ), [Value] <= _max && [Value] >= _min )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.