cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justlogmein
Helper III
Helper III

Formula for detecting local (contextual) outliers in DAX

I have created a calculated column that looks for global outliers but have found that the be insufficient as I have some data in there that, over the entire dataset, is ok but it is obviously incorrect if you look at the values around it.

justlogmein_0-1657348178536.png

I can't find a simple formula for this that I can use in DAX. Does anyone know how I could modify the DAX below so it looked at the surrounding values (excluding values that are already marked as a global outlier) and marked any anomilies?

 

=
var Q1 = 
CALCULATE
	(
	PERCENTILE.INC
		(
		'sampleDataForCalcs'[Machine Hours - Raw],0.25
		),
	FILTER
		(
		ALLEXCEPT
			(
			'sampleDataForCalcs',
			'sampleDataForCalcs'[Unit Number]
			),
		'sampleDataForCalcs'[Machine Hours - Raw] > 0
		)
	)
var Q3 = 
CALCULATE
	(
	PERCENTILE.INC
		(
		'sampleDataForCalcs'[Machine Hours - Raw],0.75
		),
	FILTER
		(
		ALLEXCEPT
			(
			'sampleDataForCalcs',
			'sampleDataForCalcs'[Unit Number]
			),
		'sampleDataForCalcs'[Machine Hours - Raw] > 0
		)
	)
var IQR = Q3 - Q1
var low = Q1 - 1.5*IQR
var high =  Q3 + 1.5*IQR
var outlier =
IF
	(
	'sampleDataForCalcs'[Machine Hours - Raw] < low
	||
	'sampleDataForCalcs'[Machine Hours - Raw] > high,
	"TRUE",
	BLANK()
	)
return outlier

 

2 REPLIES 2
justlogmein
Helper III
Helper III

Thanks, but these are not what I am looking for.

amitchandak
Super User
Super User

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors