Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
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.
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.
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
Finally the limit screening doesn't work. What goes wrong here?
@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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |