Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Finding data points outside of 2 Standard Deviations

Hello,

 

I am currently trying to to find data points of a column called "EPG" which fall outside of 2 standard deviations to remove outliers.

 

to get the Average: 

InternalEPGAvG = AVERAGE(PhenRQCJoinData[EPG])
 
To get the the std dev:
Internal St.Dev = STDEV.P(PhenRQCJoinData[EPG])
 

To find outliers in the data:

+2Std = COUNTAX(PhenRQCJoinData, PhenRQCJoinData[EPG] > PhenRQCJoinData[InternalEPGAvG] + PhenRQCJoinData[Internal St.Dev] * 2)
 
-2Std = COUNTAX(PhenRQCJoinData, PhenRQCJoinData[EPG] < PhenRQCJoinData[InternalEPGAvG] - PhenRQCJoinData[Internal St.Dev] * 2)
 
The problem is for both measures it returns the whole table (95) when it should be closer to 7.
 
Can anyone see what I am doing wrong here?
 
Here is the link to my Pbix File:
 
All Of the data and measures are on 'PhenRQCJoinData'
 
All help is greatly appreciated
 

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago
    +2Std = VAR StandardDev =
	CALCULATE(
		STDEV.P( 'PhenRQCJoinData'[EPG] ),
		REMOVEFILTERS( 'PhenRQCJoinData' )
	)
VAR OverallAverage =
	CALCULATE(
		[Internal Average],
		REMOVEFILTERS( 'PhenRQCJoinData' )
	)
VAR NumOver =
	COUNTROWS(
		FILTER(
			'PhenRQCJoinData',
			'PhenRQCJoinData'[EPG] > OverallAverage + StandardDev * 2
		)
	)
RETURN
	NumOver

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago
    +2Std = VAR StandardDev =
	CALCULATE(
		STDEV.P( 'PhenRQCJoinData'[EPG] ),
		REMOVEFILTERS( 'PhenRQCJoinData' )
	)
VAR OverallAverage =
	CALCULATE(
		[Internal Average],
		REMOVEFILTERS( 'PhenRQCJoinData' )
	)
VAR NumOver =
	COUNTROWS(
		FILTER(
			'PhenRQCJoinData',
			'PhenRQCJoinData'[EPG] > OverallAverage + StandardDev * 2
		)
	)
RETURN
	NumOver
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks heaps, Worked well.