PercentileX from averaged amount doesn't work

[Anonymous]

Hi, 

I'm quite new on DAX. I'm working on an outlier detection tool using percentile. The raw data is sort of like this, although there're much more companies and products inside of each industry : 

Industry	Company ID	Product ID	Sales Amount
BUSINESS SERVICES	1	1	10
BUSINESS SERVICES	1	2	20
BUSINESS SERVICES	2	3	30
BUSINESS SERVICES	2	4	40
COMPUTER SOFTWARE	3	5	10
COMPUTER SOFTWARE	3	6	20
COMPUTER SOFTWARE	4	7	30
COMPUTER SOFTWARE	4	8	40
ELECTRICAL EQUIPMENT	5	9	10
ELECTRICAL EQUIPMENT	5	10	20
ELECTRICAL EQUIPMENT	6	11	30
ELECTRICAL EQUIPMENT	6	12	40

I'm looking for the outliers of average product sales by company for each industry. Here's my attemp which unfortunately doesn't work (return only one value)

OutlierFlag=

var myTab= AVERAGEX(ALLSELECTED(data[Industry],data[Company ID]),SELECTEDVALUE(data[Sales amount]))

var p75=PERCENTILEX.INC(values(data[Industry]),mytab,0.75)

var p25=PERCENTILEX.INC(values(data[Industry]),mytab,0.25)

var limitUp=p75+1.5*(p75-p25)

return switch(TRUE(), SELECTEDVALUE(data[Sales amount])>limitUp, "Outlier",SELECTEDVALUE(data[Sales amount])<limitUp, "Not outlier", "Others")

I've read a great deals of posts from various sites and tested some of the proposed solutions. However nothing worked for me. Could you help me please? Thanks.

[v-eqin-msft]

Hi @Anonymous ,

1. The mytab variable could be easily changed using the following formula:

mytab = CALCULATE(AVERAGE(data[Sales Amount]),ALLEXCEPT(data,data[Company ID]))

2. Please provide me with the correct outcome for p75 and p25 to clarify your scenario.

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

[Anonymous]

Thanks for your kind reply. I've tried your tips but it doesn't work.

Actually the first line of average on sales amount by company should be like 

Measure [avgbyCompany]=AVERAGEX(VALUES(data[Company ID]),calculate(AVERAGE(data[Sales Amount])))

I've checked the avg value is correct by Company ID.

However the percentiles p75 and p25 still don't work at all. Instead of returning the percentiles, it returns the same value as the average above , whatever the percentiles, when shown alongside Industry. It seems that the Industry axis isn't recognized in the percentileX function through the measure [avgbyCompany].

Measure [p75]=PERCENTILEX.INC(values(data[Industry]), [avgbyCompany],0.75) => returns [avgbyCompany]

Measure [p50]=PERCENTILEX.INC(values(data[Industry]),[avgbyCompany],0.50) => returns [avgbyCompany]

Measure [p25]=PERCENTILEX.INC(values(data[Industry]),[avgbyCompany],0.25) => returns [avgbyCompany]

Finally the limit screening doesn't work. What goes wrong here? 

[amitchandak]

@Anonymous , Try if this small change can work

OutlierFlag=
var myTab= AVERAGEX(ALLSELECTED(data[Industry],data[Company ID]),calculate(sum(data[Sales amount])))

var p75=PERCENTILEX.INC(values(data[Industry]),mytab,0.75)

var p25=PERCENTILEX.INC(values(data[Industry]),mytab,0.25)

var limitUp=p75+1.5*(p75-p25)

return switch(TRUE(), sum(data[Sales amount])>limitUp, "Outlier",SELECTEDVALUE(data[Sales amount])<limitUp, "Not outlier", "Others")