cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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?

2 REPLIES 2
Community Support

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.

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.

Super User

@Anonymous Perhaps TRIMMEAN? TRIMMEAN - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors