Filter/Remove Highest and Lowest 10% (Percentile) from measure

[Anonymous]

Hello All, I am very new to learning this so bare with me. I have a measure which outputs the number of training sessions per instructor.

To do this I am using a DISTINCTCOUNT for both the staff identifier to identify the specific person as an instructor and not a trainee, then I divide this by the numbers of sessions:

Count Instructors divided by Count of SessionSTR =

DIVIDE([Count Sessions],[Count Instructors])

and put them in a Matrix like this to get the numbers of sessions displayed per instructor ID:

To filter out the instructors with the highest and lowest values how would I go ahead and do this? I have looked at PERCENTILE.EXC but have not succeeded in making it work. I suppose that the count above only gives me the 18.7 value in the "Total" so would it be easier to filter the table itself somehow? 

[v-chenwuz-msft]

Hi @Anonymous ,

You can create a measure to identify if this staff is in the highest or lowest values list. if yes, then the measure return blank unless 1. Then put it in filter pane on this visual. All the data of this staff will be filtered out.

Please refer this measure and adjust it to suit your case.

measure = 

var _s = summarize(allseleted('table'), [staff id], "Count Instructors divided SessionSTR",DIVIDE([Count Sessions],[Count Instructors]))

var _l_10% = PERCENTILEX.EXC(_s,[Count Instructors divided SessionSTR], 0.1)

var _h_10% = PERCENTILEX.EXC(_s,[Count Instructors divided SessionSTR], 0.9)

return
if( yourmeasure[Count Instructors divided by Count of SessionSTR] >_h_10% || yourmeasure[Count Instructors divided by Count of SessionSTR]<_l_10%, blank(),1)

If you need more help, please share your pbix file without sensitive data and expect result.

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.

[Greg_Deckler]

@Anonymous Perhaps TRIMMEAN? TRIMMEAN - Microsoft Power BI Community