Percentile Calculation based multiple criteria

[HellRaizer2310]

I need help in converting the below Excel array formula to create a custom column in PowerBI. The array formula calculates 25th, Median & 75th percentile by the industry for a specific year. Below is the dataset & the calculation in Excel.

DPO Q1 is {=PERCENTILE.INC(IF(A:A=E2,D:D),0.25)}

DPO Q2 is {=PERCENTILE.INC(IF(A:A=E2,D:D),0.50)}

DPO Q3 is {=PERCENTILE.INC(IF(A:A=E2,D:D),0.75)}

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H
Industry Group & Period_Primary	Industry Group		DPO	Industry Group & FY	DPO Q1	DPO Q2	DPO Q3
Retailing_FY2013	Retailing	FY2013	54.76141	Retailing_FY2013	30.33791	47.08711	50.92426
Retailing_FY2014	Retailing	FY2014	47.29137	Retailing_FY2014	21.93782	32.70743	39.9994
Retailing_FY2015	Retailing	FY2015	48.42403	Retailing_FY2015	20.99799	32.44873	40.43638
Retailing_FY2016	Retailing	FY2016	51.72449	Retailing_FY2016	22.10487	35.79314	43.75882
Retailing_FY2017	Retailing	FY2017	53.61568	Retailing_FY2017	29.49149	48.19909	50.90739
Retailing_FY2018	Retailing	FY2018	51.763	Retailing_FY2018	26.56472	39.45506	45.60903
Materials_FY2013	Materials	FY2013	317.5784	Materials_FY2013	67.31665	84.99168	97.15974
Materials_FY2014	Materials	FY2014	257.919	Materials_FY2014	77.98812	86.07956	215.0012
Materials_FY2015	Materials	FY2015	342.1701	Materials_FY2015	79.85165	91.58489	129.6097
Materials_FY2016	Materials	FY2016	291.9873	Materials_FY2016	74.52159	85.53263	126.8878
Materials_FY2017	Materials	FY2017	320.1319	Materials_FY2017	65.81055	84.59221	103.2461
Materials_FY2018	Materials	FY2018	273.0419	Materials_FY2018	68.04384	74.0075	80.3267
Food and Staples Retailing_FY2013	Food and Staples Retailing	FY2013	34.70309	Food and Staples Retailing_FY2013	36.39186	38.08062	39.76939
Food and Staples Retailing_FY2014	Food and Staples Retailing	FY2014	37.65668	Food and Staples Retailing_FY2014	39.19071	40.72474	42.25876
Food and Staples Retailing_FY2015	Food and Staples Retailing	FY2015	42.99557	Food and Staples Retailing_FY2015	42.56142	42.70614	42.85085
Food and Staples Retailing_FY2016	Food and Staples Retailing	FY2016	45.67189	Food and Staples Retailing_FY2016	41.76539	43.06756	44.36972
Food and Staples Retailing_FY2017	Food and Staples Retailing	FY2017	48.48932	Food and Staples Retailing_FY2017	44.50554	45.83347	47.1614
Food and Staples Retailing_FY2018	Food and Staples Retailing	FY2018	48.20002	Food and Staples Retailing_FY2018	51.7214	55.24279	58.76417
Telecommunication Services_FY2013	Telecommunication Services	FY2013	44.50132	Telecommunication Services_FY2013	44.50132	44.50132	44.50132
Telecommunication Services_FY2014	Telecommunication Services	FY2014	38.67286	Telecommunication Services_FY2014	38.67286	38.67286	38.67286
Telecommunication Services_FY2015	Telecommunication Services	FY2015	39.81588	Telecommunication Services_FY2015	39.81588	39.81588	39.81588
Telecommunication Services_FY2016	Telecommunication Services	FY2016	44.2328	Telecommunication Services_FY2016	44.2328	44.2328	44.2328
Telecommunication Services_FY2017	Telecommunication Services	FY2017	33.95016	Telecommunication Services_FY2017	33.95016	33.95016	33.95016
Telecommunication Services_FY2018	Telecommunication Services	FY2018	42.16031	Telecommunication Services_FY2018	42.16031	42.16031	42.16031
Transportation_FY2013	Transportation	FY2013	20.13272	Transportation_FY2013	31.40442	42.67612	53.94781
Transportation_FY2014	Transportation	FY2014	18.5204	Transportation_FY2014	29.74765	40.97489	52.20213
Transportation_FY2015	Transportation	FY2015	0	Transportation_FY2015	0	0	0
Transportation_FY2016	Transportation	FY2016	0	Transportation_FY2016	0	0	0
Transportation_FY2017	Transportation	FY2017	0	Transportation_FY2017	0	0	0
Transportation_FY2018	Transportation	FY2018	0	Transportation_FY2018	0	0	0
Utilities_FY2013	Utilities	FY2013	62.75198	Utilities_FY2013	62.75198	62.75198	62.75198
Utilities_FY2014	Utilities	FY2014	28.46722	Utilities_FY2014	28.46722	28.46722	28.46722
Utilities_FY2015	Utilities	FY2015	31.06284	Utilities_FY2015	31.06284	31.06284	31.06284
Utilities_FY2016	Utilities	FY2016	40.72178	Utilities_FY2016	40.72178	40.72178	40.72178
Utilities_FY2017	Utilities	FY2017	35.99206	Utilities_FY2017	35.99206	35.99206	35.99206
Utilities_FY2018	Utilities	FY2018	37.88343	Utilities_FY2018	37.88343	37.88343	37.88343
Food and Staples Retailing_FY2013	Food and Staples Retailing	FY2013	41.45815	Food and Staples Retailing_FY2013	36.39186	38.08062	39.76939
Food and Staples Retailing_FY2014	Food and Staples Retailing	FY2014	43.79279	Food and Staples Retailing_FY2014	39.19071	40.72474	42.25876
Food and Staples Retailing_FY2015	Food and Staples Retailing	FY2015	42.4167	Food and Staples Retailing_FY2015	42.56142	42.70614	42.85085
Food and Staples Retailing_FY2016	Food and Staples Retailing	FY2016	40.46323	Food and Staples Retailing_FY2016	41.76539	43.06756	44.36972
Food and Staples Retailing_FY2017	Food and Staples Retailing	FY2017	43.17762	Food and Staples Retailing_FY2017	44.50554	45.83347	47.1614
Food and Staples Retailing_FY2018	Food and Staples Retailing	FY2018	62.28555	Food and Staples Retailing_FY2018	51.7214	55.24279	58.76417
Commercial and Professional Services_FY2013	Commercial and Professional Services	FY2013	21.43894	Commercial and Professional Services_FY2013	28.59273	35.74651	42.9003
Commercial and Professional Services_FY2014	Commercial and Professional Services	FY2014	19.99504	Commercial and Professional Services_FY2014	27.03937	34.08369	41.12801
Commercial and Professional Services_FY2015	Commercial and Professional Services	FY2015	22.23747	Commercial and Professional Services_FY2015	29.1385	36.03952	42.94055
Commercial and Professional Services_FY2016	Commercial and Professional Services	FY2016	22.09804	Commercial and Professional Services_FY2016	27.7465	33.39496	39.04342
Commercial and Professional Services_FY2017	Commercial and Professional Services	FY2017	30.14668	Commercial and Professional Services_FY2017	33.81834	37.48999	41.16164
Commercial and Professional Services_FY2018	Commercial and Professional Services	FY2018	23.06502	Commercial and Professional Services_FY2018	29.95296	36.84091	43.72885
Materials_FY2013	Materials	FY2013	79.98076	Materials_FY2013	67.31665	84.99168	97.15974
Materials_FY2014	Materials	FY2014	86.24756	Materials_FY2014	77.98812	86.07956	215.0012
Materials_FY2015	Materials	FY2015	88.27918	Materials_FY2015	79.85165	91.58489	129.6097
Materials_FY2016	Materials	FY2016	87.81311	Materials_FY2016	74.52159	85.53263	126.8878
Materials_FY2017	Materials	FY2017	85.22238	Materials_FY2017	65.81055	84.59221	103.2461
Materials_FY2018	Materials	FY2018	76.32487	Materials_FY2018	68.04384	74.0075	80.3267
Pharmaceuticals, Biotechnology and Life Sciences_FY2013	Pharmaceuticals, Biotechnology and Life Sciences	FY2013	39.85176	Pharmaceuticals, Biotechnology and Life Sciences_FY2013	39.85176	39.85176	39.85176
Pharmaceuticals, Biotechnology and Life Sciences_FY2014	Pharmaceuticals, Biotechnology and Life Sciences	FY2014	29.98214	Pharmaceuticals, Biotechnology and Life Sciences_FY2014	29.98214	29.98214	29.98214
Pharmaceuticals, Biotechnology and Life Sciences_FY2015	Pharmaceuticals, Biotechnology and Life Sciences	FY2015	36.10921	Pharmaceuticals, Biotechnology and Life Sciences_FY2015	36.10921	36.10921	36.10921
Pharmaceuticals, Biotechnology and Life Sciences_FY2016	Pharmaceuticals, Biotechnology and Life Sciences	FY2016	36.3866	Pharmaceuticals, Biotechnology and Life Sciences_FY2016	36.3866	36.3866	36.3866
Pharmaceuticals, Biotechnology and Life Sciences_FY2017	Pharmaceuticals, Biotechnology and Life Sciences	FY2017	43.74212	Pharmaceuticals, Biotechnology and Life Sciences_FY2017	43.74212	43.74212	43.74212

[v-xicai]

Hi  @HellRaizer2310 ,

You may create What if parameter to set percentile, the Add slicer to this page checkbox automatically puts a slicer with your What if parameter onto the current report page.

Then you can create measure like DAX below. Once you move the slider, the new created Measure1 will reflect the result.

Meaure1= Table1[DPO]*Parameter[Parameter Value]

You can learn more:https://docs.microsoft.com/en-us/power-bi/desktop-what-if.

Best Regards,

Amy

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

[HellRaizer2310]

@v-xicai 

Hello Amy,

Thank you for the response. The solution you shared probably works at the visual level, but I am trying to add columns in the dataset. So I already have column A to E in the dataset and am trying to create column F thru H in the dataset itself. I will then create a visualization showing the trend line of the data for each industry over the years. Currently I am doing these array calculation in Excel and pulling in the data, however, these array formulas affect the performance of the excel and the report itself. Can you guide me in creating the new columns in the PowerBI either using DAX queries or M queries?

Best Regards,

Amit