How to I calculate the Percentile using a measure?

[ERing]

I have a report with a visual like below that shows survey results for sales representatives.

I've been asked to provide a conditional formatting of the Delta column, using percentile to set the conditions for the formatting.

To acheive this, my first step is creating measures for the percentiles I'll be using.

The issue I'm facing is that each of the three columns in my visual is a measure and my understanding is that the PERCENTILE function requires a column.

I need help in how to calculate the 75th percentile 

Score for Opportunities Closed = 
CALCULATE(
DIVIDE(SUM(Table[Scores]),COUNT(Table[Opportunity_ID])),
KEEPFILTERS(TABLE[Closed_Flag = 1))

Score for Opportunities Not Closed = 
CALCULATE(
DIVIDE(SUM(Table[Scores]),COUNT(Table[Opportunity_ID])),
KEEPFILTERS(TABLE[Closed_Flag = 0))

Delta = [Score for Opportunities Not Closed] - [Score for Opportunities Closed]

This the my matrix visual layout

This is how my data is structured:

Opportunity_ID	Score_Subcategory	Score_Category	Closed_Flag
1	Setting Agenda	Process	1
1	Budgeting	Process	1
1	Call Manager	Process	1
1	Offer Survey	Process	1
1	Analysis	Process	1
1	Guarantee	Process	1
1	Referral	Process	1
1	Scheduling	Process	1
1	Sitdown	Process	1
1	Building Rapport	Skills	1
1	Closing	Skills	1
1	Find a need	Skills	1
1	Objection Handling	Skills	1
2	Setting Agenda	Process	0
2	Budgeting	Process	0
2	Call Manager	Process	0
2	Offer Survey	Process	0
2	Analysis	Process	0
2	Guarantee	Process	0
2	Referral	Process	0
2	Scheduling	Process	0
2	Sitdown	Process	0
2	Building Rapport	Skills	0
2	Closing	Skills	0
2	Find a need	Skills	0
2	Objection Handling	Skills	0

[v-pagayam-msft]

Hi @ERing ,
Thank you @maruthisp and @SreeniBattula for the helpful responses!

I tried to recreate it on my local upon my understanding.Please refer the screenshot and file for your reference.

If this answer meets your requirements,give us kudos and consider accepting it as solution. If still require further assistance, feel free to reachout!

Thank you.

Regards,
Pallavi

[ERing]

Thanks @v-pagayam-msft  I have found the solution elsewhere.

[v-pagayam-msft]

Hi @ERing ,
Thank you for the update!Glad you found a solution. Please share your answer and accept it as solution so others in the community who faces similar issues may find it easily.

Thank you for your understanding.

[ERing · Accepted solution]

The solution was to use PERCENTILEX with ALLSELECTED for both Dimensions. So the measure I ended up with is: 

PERCENTILE75 = PERCENTILEX.EXC(ALLSELECTED('Table'[Score_Category],'Table'[Score_Subcategory),'Table'[Delta],.75)

[maruthisp]

Hi ERing,

I tried to implement a solution based on your original post description.
Please check the below pbix file:
calculate the Percentile using a measure.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ERing]

Thanks @maruthisp , however this doesn't provide the expcted solution. The expected value should be 3.83.

I sorted the values in the Delta column. The 75th percentile would be equal to the 9th number. 

So the value in Delta_75th_Percentile should be 3.83 for all the values in that colum.

-30.00, -15.00, -9.00, -8.00, -7.40, -7.00, -7.00, -6.00, 3.83, 10.00, 20.00

[SreeniBattula]

Delta is % of Opportunities Not Closed vs Opportunities Closed? if not can you  provide me the expected out put in  the table .

[ERing]

@SreeniBattula Delta is Opportunities Not Closed - Opportunities Closed. Simply the difference between the two calculations.

[SreeniBattula]

Create "New measure" using the below DAX.

Delta =
DIVIDE(
[Score for Opportunities Not Closed] - [Score for Opportunities Closed]
,
[Score for Opportunities Closed]
)

Format the measure as a percentage:

[ERing]

@SreeniBattula I'm sorry but I don't follow how that provides a solution to my problem.