Percentile calculation issue

Question

I have this table below for comparison, Excel value are the expected one however Power BI result returns some incorrect values (italicized values).

Formula Used is

95th Percentile = PERCENTILE.INC('Consolidated Data'[Value], 0.95)

Anyone knows how to solve this?

Table:

MTree	Percentile	Excel 95%	Power BI 95th Percentile
Data 1	Aug-19	12264.06	8212.71
Data 1	Sep-19	18670.48	9947.2
Data 1	Oct-19	12300.58	7252.64
Data 2	Oct-19	49.8	49.8
Data 3	Mar-18	16698.42	16698.415
Data 3	Apr-18	27378	27378
Data 3	May-18	36996.54	36996.54
Data 3	Jun-18	33791	33791
Data 3	Jul-18	30843.94	30843.94
Data 3	Aug-18	35029.95	35029.95
Data 3	Sep-18	32458.44	32458.44
Data 3	Oct-18	13437.37	13437.365
Data 3	Nov-18	11427.16	11427.155
Data 3	Dec-18	8884.68	8884.68
Data 3	Jan-19	8556.735	8556.735
Data 3	Feb-19	8330.7	8330.7
Data 3	Mar-19	8849.86	8849.86
Data 3	Apr-19	8888.59	8888.59
Data 3	May-19	9914.83	9914.83
Data 3	Jun-19	12544.46	12544.46
Data 3	Jul-19	19229.32	10870.32
Data 3	Aug-19	22867.84	13159.62
Data 3	Sep-19	29982.28	15308.7
Data 3	Oct-19	31579.46	15946.26

v-joesh-msft · Answer

Hi&nbsp;Seira088&nbsp;,You can check if there is a 0 in the field 'Consolidated Data'[Value] in the Query Editor, and if so, replace it with null. Or have you made some other conversions?If it doesn't meet your requirement, kindly share your sample data if you don't have any Confidential Information.&nbsp;Best Regards,Community Support Team _ JoeyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

seira088 · Answer

Hi&nbsp;v-joesh-msft&nbsp;,I actually filtered out 0 values. so no 0 value is present in the 'Consolidated Data'[Value] column.

v-joesh-msft · Answer

Hi Seira088&nbsp;,Could you share your sample data for us to have a test if you don't have any Confidential Information?&nbsp;Best Regards,Community Support Team _ JoeyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

seira088 · Answer

Hi&nbsp;v-joesh-msft&nbsp;,here's the data:DataDateValueData 106/08/2019 0:008.9Data 107/08/2019 0:0012.8Data 108/08/2019 0:00394.6Data 109/08/2019 0:00427.9Data 110/08/2019 0:00771.2Data 111/08/2019 0:001599.8Data 112/08/2019 0:00799.7Data 113/08/2019 0:005253.6Data 115/08/2019 0:005972.6Data 116/08/2019 0:006472.5Data 117/08/2019 0:006611.5Data 118/08/2019 0:0013257.2Data 119/08/2019 0:006631Data 120/08/2019 0:006632.7Data 121/08/2019 0:006651.5Data 122/08/2019 0:007063.5Data 123/08/2019 0:007064.4Data 124/08/2019 0:007675.8Data 125/08/2019 0:0015892.8Data 126/08/2019 0:007947.1Data 127/08/2019 0:007948.8Data 128/08/2019 0:008048.1Data 129/08/2019 0:008094.8Data 130/08/2019 0:008276.2Data 131/08/2019 0:008291.5Data 101/09/2019 0:0017500.8Data 102/09/2019 0:008965.3Data 103/09/2019 0:008967.9Data 105/09/2019 0:009013.4Data 106/09/2019 0:009014Data 107/09/2019 0:009028.1Data 108/09/2019 0:0018056.8Data 109/09/2019 0:009033.2Data 110/09/2019 0:009033.7Data 111/09/2019 0:009061.1Data 112/09/2019 0:009111.6Data 113/09/2019 0:009151.4Data 114/09/2019 0:009154.8Data 115/09/2019 0:0018451.6Data 116/09/2019 0:009317.5Data 117/09/2019 0:009318.7Data 118/09/2019 0:009341Data 119/09/2019 0:009370.3Data 120/09/2019 0:009392.1Data 121/09/2019 0:009406Data 122/09/2019 0:0018816.4Data 123/09/2019 0:009408.1Data 124/09/2019 0:009409.5Data 125/09/2019 0:009514Data 126/09/2019 0:009719.2Data 127/09/2019 0:009776.2Data 128/09/2019 0:009804.6Data 129/09/2019 0:0019894.4Data 130/09/2019 0:009949.9Data 201/08/2019 0:009383.1Data 202/08/2019 0:009383.1Data 203/08/2019 0:009372.5Data 204/08/2019 0:0018723.6Data 206/08/2019 0:009353.6Data 207/08/2019 0:009558.1Data 208/08/2019 0:0010420.9Data 209/08/2019 0:0010626.5Data 210/08/2019 0:0010762.1Data 211/08/2019 0:0021776.8Data 212/08/2019 0:0011037.3Data 213/08/2019 0:0011095.6Data 215/08/2019 0:0011419.9Data 216/08/2019 0:0011550.3Data 217/08/2019 0:0011684.6Data 218/08/2019 0:0023595.2Data 219/08/2019 0:0011968Data 220/08/2019 0:0012070.5Data 221/08/2019 0:0012245.1Data 222/08/2019 0:0012348.1Data 223/08/2019 0:0012455.8Data 224/08/2019 0:0012577.1Data 225/08/2019 0:0025384.2Data 226/08/2019 0:0012817.3Data 227/08/2019 0:0012917.5Data 228/08/2019 0:0013025.3Data 229/08/2019 0:0013118.7Data 230/08/2019 0:0013221Data 231/08/2019 0:0013306.4Data 201/09/2019 0:0026852.8Data 202/09/2019 0:0013577.2Data 203/09/2019 0:0013673.6Data 205/09/2019 0:0013983.9Data 206/09/2019 0:0013975Data 207/09/2019 0:0013989.5Data 208/09/2019 0:0028010.2Data 209/09/2019 0:0013966.3Data 210/09/2019 0:0014147.5Data 211/09/2019 0:0014254.6Data 212/09/2019 0:0014330.5Data 213/09/2019 0:0014422.7Data 214/09/2019 0:0014521.2Data 215/09/2019 0:0029233.6Data 216/09/2019 0:0014709.9Data 217/09/2019 0:0014818.5Data 218/09/2019 0:0014883.1Data 219/09/2019 0:0014981.5Data 220/09/2019 0:0015061.8Data 221/09/2019 0:0015139.8Data 222/09/2019 0:0030481.4Data 223/09/2019 0:0015264.3Data 224/09/2019 0:0015264.4Data 225/09/2019 0:0015250.2Data 226/09/2019 0:0015250.9Data 227/09/2019 0:0015272.2Data 228/09/2019 0:0015292.8Data 229/09/2019 0:0030617.4Data 230/09/2019 0:0015334

v-joesh-msft · Answer

Hi&nbsp;Seira088&nbsp;,Based on your sample data, I get the correct result. Here is an example I made, you can see if there is a difference with your data.If your problem is still not resolved, it will be helpful to share your sample pbix file.Here is a demo, please try it:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQm1f8bg8ytHkVoVIpdhn7EBaYoL_NVi9yCt7aVyvxTU7A?e=Sh7EYYBest Regards,Community Support Team _ JoeyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;&nbsp;

Forum Discussion

Percentile calculation issue

5 Replies

Related content

Calculate percentile between tables

Calculate percentile from measure

Matrix calculation Issue - DAX

Calculating percentiles of a measure

Percentile Calculation