Filtering # of rows by the result of a measure

[meconnolly95]

I have a data set that I am trying to show top 20%, Middle 60%, and Bottom 20% of a count of # of clients. 

I created a measure that returns the top or bottom 20%, by doing a distinctcount(Client Name)*.20. For example - if someone has a client count of 134, I would want the table to show the top 27 accounts by revenue size. But the total client count is always changing, so need it to be dynamic on the count, so I cant use the Top N function.

How do I filter the table down to show the result of that measure? Open to other suggestions as well.

Thanks!

[v-chenwuz-msft · Accepted solution]

Hi @meconnolly95 ,

I recommend PERCENTILE.INC(<column>, <k>).

You can create a measure like the following,

80%measure =
IF( table[value] > PERCENTILE.INC( table[value], 0.8 ), 1, BLANK() )

Then add the 80%measure in the Filters and set it items is 1 to filter the result you want.

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.

[amitchandak]

@meconnolly95 , see if these can help a bit

Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459