Help with calculating percentiles of values from a measure that uses columns from two data sources?

[BadAtDAX]

Hi all,

I'm struggling with the DAX for a percentile calculation measure.  In my Item data source I have (among other columns) Item ID and Manufacturing Location.

In my Defect data source I have (among other other columns) Defect ID, Defect Type, and Item ID.

I made a measure for defect percentage as Defect % = DISTINCTCOUNT('Defect'[Item ID]) / DISTINCTCOUNT('Item'[Item ID]).

My goal is to find the percentile of each manufacturing location's Defect % so that I can then calculate the mean and standard deviations of the distribution of Defect % for other calculations.

I'm having a lot of trouble with the DAX for this, so any help is greatly appreciated.

[Mahesh0016]

@BadAtDAX can you please share expected output in table for more understanding.Thank You!!

[BadAtDAX]

Sure, my expected output is as follows:

Manufacturing Location	Defect %	Defect % Percentile
New York	2%	0.00%
Los Angeles	6%	60.00%
San Francisco	2%	0.00%
Chicago	8%	100.00%
Philadelphia	7%	80.00%
Detroit	3%	40.00%

[v-zhangti]

Hi, @BadAtDAX 

Can you provide some of the source data, or explain how the expected output was obtained?

Best Regards