How to calculate 5th Percentile

[Cevola]

Hi All,

I have the following data and I want to write a measure to calculate 5th percentile of "Value" columns. 

Note: I want all the ID's with values falling in the 5th percentile.

Id	Value	%GT Value
A1	2.993857962	2.88%
A2	4.790980789	4.61%
A3	5.285423474	5.08%
A4	9.845946214	9.47%
A5	13.1619472	12.65%
A6	3.061712719	2.94%
A7	14.10853515	13.56%
A8	20.40455305	19.62%
A9	8.082005991	7.77%
A10	0.927973851	0.89%
A11	9.357774268	9.00%
A12	12.00309514	11.54%

Thanks for your help.

[v-yueyunzh-msft · Accepted solution]

Hi , @Cevola 

According to your description, you want to "calculate 5th percentile and show all the ID's with values falling in the 5th percentile".

We can create a measure to get the 5th percentile :

5th Percentile = 
var _t = SUMMARIZE( ALLSELECTED('Table') , 'Table'[Id] , "value" , CALCULATE( SUM('Table'[Value])))
return
 PERCENTILEX.INC(_t , [value] , 0.5)

If you want to show all IDS which is less then the 5th percentile , we can create another measure:

Measure = IF( MAX('Table'[Value]) <= [5th Percentile] ,1, -1)

Then we can put this measure on the "Filter on this visual":

If I misunderstood what you mean, you can tell me your detailed calculation process and logic, and provide me with your final desired result in the form of a table, so that we can help you better！

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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