Percentail calculation from Excel to power bi

[rasalaprashanth]

Hello All, 

I have a Percentile calculation in excel that i need to replicate in power bi 

when i use the function in power bi i am getting a wrong value  

UNIT_ID	Usage	Annual Data Point	Year	In Service Fiscal year	Et Type		Percentile
35985	-392	1	2023	2009	Wheel		5%	90
35936	0	1	2023	2007	Wheel		10%	112
36367	0	1	2023	2008	Wheel		15%	144
49532	0	1	2023	2015	Wheel		20%	167
38212	7	1	2023	2011	Wheel		25%	184
51612	8	1	2023	2016	Wheel		30%	195
60780	10	1	2023	2020	Wheel		35%	211
38206	15	1	2023	2010	Wheel		40%	232
35934	40	1	2023	2007	Wheel		45%	252
54569	57	1	2023	2017	Wheel		50%	271
36015	60	1	2023	2008	Wheel		55%	295
36173	77	1	2023	2007	Wheel		60%	315
51604	83	1	2023	2016	Wheel		65%	327
41290	84	1	2023	2012	Wheel		70%	347
38213	93	1	2023	2011	Wheel		75%	377
51605	94	1	2023	2016	Wheel		80%	407
38210	95	1	2023	2011	Wheel		85%	456
62678	97	1	2023	2021	Wheel		90%	532
51595	100	1	2023	2016	Wheel		95%	726

Excel Calculation for is = =PERCENTILE.EXC(B6:B218,H2) for 5% 

here is an sample data from excel 

in power bi we are getting the Et type filter from a different column 

my power bi calculation is 

5 % percentile = PERCENTILE.EXC('Usage'[Usage],0.05)

in excel we are getting for 5% is 90

in power bi i am getting for 5% is 0

How can i replicate excel function in power bi  

[v-shex-msft · Accepted solution]

HI @rasalaprashanth,

It seems like you exclude the zero and negative values from excel calculation ranges, so I'd like to suggest you to use 'PERCENTILE.EXC' function to calculate ranges with correspond conditions:

5 % percentile =
PERCENTILEX.EXC (
    FILTER ( ALLSELECTED ( 'Usage'[Usage] ), 'Usage'[Usage] > 0 ),
    'Usage'[Usage],
    0.05
)

PERCENTILEX.EXC – DAX Guide

Regards,

Xiaoxin Sheng