Re: Percentile calculation issue

Seira088 · ‎10-15-2019

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 · ‎10-16-2019

Hi @Seira088 ,

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.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Seira088 · ‎10-16-2019

Hi @v-joesh-msft ,

I actually filtered out 0 values. so no 0 value is present in the 'Consolidated Data'[Value] column.

v-joesh-msft · ‎10-16-2019

Hi @Seira088 ,

Could you share your sample data for us to have a test if you don't have any Confidential Information?

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Seira088 · ‎10-17-2019

Hi @v-joesh-msft ,

here's the data:

Data	Date	Value
Data 1	06/08/2019 0:00	8.9
Data 1	07/08/2019 0:00	12.8
Data 1	08/08/2019 0:00	394.6
Data 1	09/08/2019 0:00	427.9
Data 1	10/08/2019 0:00	771.2
Data 1	11/08/2019 0:00	1599.8
Data 1	12/08/2019 0:00	799.7
Data 1	13/08/2019 0:00	5253.6
Data 1	15/08/2019 0:00	5972.6
Data 1	16/08/2019 0:00	6472.5
Data 1	17/08/2019 0:00	6611.5
Data 1	18/08/2019 0:00	13257.2
Data 1	19/08/2019 0:00	6631
Data 1	20/08/2019 0:00	6632.7
Data 1	21/08/2019 0:00	6651.5
Data 1	22/08/2019 0:00	7063.5
Data 1	23/08/2019 0:00	7064.4
Data 1	24/08/2019 0:00	7675.8
Data 1	25/08/2019 0:00	15892.8
Data 1	26/08/2019 0:00	7947.1
Data 1	27/08/2019 0:00	7948.8
Data 1	28/08/2019 0:00	8048.1
Data 1	29/08/2019 0:00	8094.8
Data 1	30/08/2019 0:00	8276.2
Data 1	31/08/2019 0:00	8291.5
Data 1	01/09/2019 0:00	17500.8
Data 1	02/09/2019 0:00	8965.3
Data 1	03/09/2019 0:00	8967.9
Data 1	05/09/2019 0:00	9013.4
Data 1	06/09/2019 0:00	9014
Data 1	07/09/2019 0:00	9028.1
Data 1	08/09/2019 0:00	18056.8
Data 1	09/09/2019 0:00	9033.2
Data 1	10/09/2019 0:00	9033.7
Data 1	11/09/2019 0:00	9061.1
Data 1	12/09/2019 0:00	9111.6
Data 1	13/09/2019 0:00	9151.4
Data 1	14/09/2019 0:00	9154.8
Data 1	15/09/2019 0:00	18451.6
Data 1	16/09/2019 0:00	9317.5
Data 1	17/09/2019 0:00	9318.7
Data 1	18/09/2019 0:00	9341
Data 1	19/09/2019 0:00	9370.3
Data 1	20/09/2019 0:00	9392.1
Data 1	21/09/2019 0:00	9406
Data 1	22/09/2019 0:00	18816.4
Data 1	23/09/2019 0:00	9408.1
Data 1	24/09/2019 0:00	9409.5
Data 1	25/09/2019 0:00	9514
Data 1	26/09/2019 0:00	9719.2
Data 1	27/09/2019 0:00	9776.2
Data 1	28/09/2019 0:00	9804.6
Data 1	29/09/2019 0:00	19894.4
Data 1	30/09/2019 0:00	9949.9
Data 2	01/08/2019 0:00	9383.1
Data 2	02/08/2019 0:00	9383.1
Data 2	03/08/2019 0:00	9372.5
Data 2	04/08/2019 0:00	18723.6
Data 2	06/08/2019 0:00	9353.6
Data 2	07/08/2019 0:00	9558.1
Data 2	08/08/2019 0:00	10420.9
Data 2	09/08/2019 0:00	10626.5
Data 2	10/08/2019 0:00	10762.1
Data 2	11/08/2019 0:00	21776.8
Data 2	12/08/2019 0:00	11037.3
Data 2	13/08/2019 0:00	11095.6
Data 2	15/08/2019 0:00	11419.9
Data 2	16/08/2019 0:00	11550.3
Data 2	17/08/2019 0:00	11684.6
Data 2	18/08/2019 0:00	23595.2
Data 2	19/08/2019 0:00	11968
Data 2	20/08/2019 0:00	12070.5
Data 2	21/08/2019 0:00	12245.1
Data 2	22/08/2019 0:00	12348.1
Data 2	23/08/2019 0:00	12455.8
Data 2	24/08/2019 0:00	12577.1
Data 2	25/08/2019 0:00	25384.2
Data 2	26/08/2019 0:00	12817.3
Data 2	27/08/2019 0:00	12917.5
Data 2	28/08/2019 0:00	13025.3
Data 2	29/08/2019 0:00	13118.7
Data 2	30/08/2019 0:00	13221
Data 2	31/08/2019 0:00	13306.4
Data 2	01/09/2019 0:00	26852.8
Data 2	02/09/2019 0:00	13577.2
Data 2	03/09/2019 0:00	13673.6
Data 2	05/09/2019 0:00	13983.9
Data 2	06/09/2019 0:00	13975
Data 2	07/09/2019 0:00	13989.5
Data 2	08/09/2019 0:00	28010.2
Data 2	09/09/2019 0:00	13966.3
Data 2	10/09/2019 0:00	14147.5
Data 2	11/09/2019 0:00	14254.6
Data 2	12/09/2019 0:00	14330.5
Data 2	13/09/2019 0:00	14422.7
Data 2	14/09/2019 0:00	14521.2
Data 2	15/09/2019 0:00	29233.6
Data 2	16/09/2019 0:00	14709.9
Data 2	17/09/2019 0:00	14818.5
Data 2	18/09/2019 0:00	14883.1
Data 2	19/09/2019 0:00	14981.5
Data 2	20/09/2019 0:00	15061.8
Data 2	21/09/2019 0:00	15139.8
Data 2	22/09/2019 0:00	30481.4
Data 2	23/09/2019 0:00	15264.3
Data 2	24/09/2019 0:00	15264.4
Data 2	25/09/2019 0:00	15250.2
Data 2	26/09/2019 0:00	15250.9
Data 2	27/09/2019 0:00	15272.2
Data 2	28/09/2019 0:00	15292.8
Data 2	29/09/2019 0:00	30617.4
Data 2	30/09/2019 0:00	15334

v-joesh-msft · ‎10-18-2019

Hi @Seira088 ,

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/EQm1f8bg8ytHkVoVIp...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.