cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alyseeliu
Regular Visitor

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.

3 REPLIES 3
Eyelyn9
Community Support
Community Support

Hi @alyseeliu ,

 

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

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

mytab.PNG

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.

alyseeliu
Regular Visitor

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
Super User
Super User

@alyseeliu , 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")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.