Need help to replicate Excel formula using DAX

[AnkitaaMishra]

Hi All, 
I am doing few calculation in excel which I would like to replicate using DAX in Power BI : 
Below is the raw table in excel : 

Account	Account Means
Account 1	89.30951472
Account 2	84.44998398
Account 3	48.44260215
Account 4	76.18483461
Account 5	6.54566581
Account 6	80.57958041
Account 7	80.82168525
Account 8	57.33064507
Account 9	17.96370662
Account 10	84.11002185
Account 11	31.05410716
Account 12	80.00578869
Account 13	91.57964664
Account 14	50.0811584
Account 15	91.73717523
Account 16	31.5117545
Account 17	53.57517991
Account 18	1.5190093
Account 19	90.34303699
Account 20	85.8063106
Account 21	20.31086152
Account 22	30.34881251
Account 23	50.28171327
Account 24	54.11314484
Account 25	84.17643827
Account 26	20.89591916
Account 27	11.00972825
Account 28	81.47025708
Account 29	78.22208867
Account 30	75.05752564

Next I am calculating percentile and getting below result as Percentile table (using Percentile.INC DAX formula in Power BI) : 

Percentile	Percentile Value
0.05	8.55449391
0.1	17.26830879
0.15	20.51563169
0.2	28.45823384
0.25	31.16851899
0.3	43.36334786
0.35	50.11124163
0.4	52.25779326
0.45	54.27401985
0.5	66.19408536
0.55	76.12846916
0.6	78.93556868
0.65	80.49351165
0.7	81.0162568
0.75	83.45008065
0.8	84.23114741
0.85	85.33159628
0.9	89.41286695
0.95	91.0231723
1	91.73717523

After that below is the Formula I am using which I am unable to replicate in DAX for the highlighted row in snapshot :
=AVERAGEIFS($D$5:$D$34,$D$5:$D$34,"<"&INDEX($H$5:$H$24,MATCH($L5,$G$5:$G$24,0)))

Thanks,
Ankita

[MFelix · Accepted solution]

Hi @AnkitaaMishra ,

Not sure if the value you show in the last image are correct however this can be done in the following way:

• Create a separate table for the percentile then add the folllowing measures:

Percentile value = PERCENTILE.INC('Accounts'[Account Means], SELECTEDVALUE('Percentile'[Percentile]))

Average Value = 
 var PercentileTotal = [Percentile value]
 var temp_table = FILTER('Accounts', 'Accounts'[Account Means] < PercentileTotal)
 Return
  AVERAGEX(temp_table, 'Accounts'[Account Means])

If you want to have a third table with the benchmark redo the Average Value to:

Average Value benchmar = 
 var PercentileTotal = CALCULATE([Percentile value] , Percentile[Percentile] = SELECTEDVALUE('Percentile Benchmark'[Percentile Benchmark]))
 var temp_table = FILTER('Accounts', 'Accounts'[Account Means] < PercentileTotal)
 Return
  AVERAGEX(temp_table, 'Accounts'[Account Means])

See file attach

[MFelix · Accepted solution]

Hi @AnkitaaMishra ,

Not sure if the value you show in the last image are correct however this can be done in the following way:

• Create a separate table for the percentile then add the folllowing measures:

Percentile value = PERCENTILE.INC('Accounts'[Account Means], SELECTEDVALUE('Percentile'[Percentile]))

Average Value = 
 var PercentileTotal = [Percentile value]
 var temp_table = FILTER('Accounts', 'Accounts'[Account Means] < PercentileTotal)
 Return
  AVERAGEX(temp_table, 'Accounts'[Account Means])

If you want to have a third table with the benchmark redo the Average Value to:

Average Value benchmar = 
 var PercentileTotal = CALCULATE([Percentile value] , Percentile[Percentile] = SELECTEDVALUE('Percentile Benchmark'[Percentile Benchmark]))
 var temp_table = FILTER('Accounts', 'Accounts'[Account Means] < PercentileTotal)
 Return
  AVERAGEX(temp_table, 'Accounts'[Account Means])

See file attach

[AnkitaaMishra]

Hello @MFelix ,
Thanks for your reply, This solution is working for me.

I need additional help here. 
Actually I have multiple slicers added in the report which are not driving these percentile value, its coming constant. I checked its not relationship issue. for example If any year is selected then also the values are same.

Is there any way we could calculate this dynamically?

Thanks,
Ankita

[MFelix · Accepted solution]

Hi @AnkitaaMishra ,

Not sure how you have the rest of the model but you should change the Percentile calculation to something similar to this:

Percentile value = CALCULATE(PERCENTILE.INC('Accounts'[Account Means], SELECTEDVALUE('Percentile'[Percentile])), ALLSELECTED(Accounts[Account]))

[AnkitaaMishra]

Hi @danextian , thanks for your reply.
I am using Percentile excel formula to calculate second table

[danextian]

Hi @AnkitaaMishra 

What specific formula did you use to get the second table? And is that the result of a calculated table. I am asking because I can't seem to replicate the result using PERCENTILE.INC on your sample data.