Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

PercentileX from averaged amount doesn't work

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 : 

IndustryCompany IDProduct IDSales Amount
BUSINESS SERVICES1110
BUSINESS SERVICES1220
BUSINESS SERVICES2330
BUSINESS SERVICES2440
COMPUTER SOFTWARE3510
COMPUTER SOFTWARE3620
COMPUTER SOFTWARE4730
COMPUTER SOFTWARE4840
ELECTRICAL EQUIPMENT5910
ELECTRICAL EQUIPMENT51020
ELECTRICAL EQUIPMENT61130
ELECTRICAL EQUIPMENT61240

 

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.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.