Forum Discussion
Identify Outliers
Hello,
My ultimate goal is to create a measure that identifies if a data point is an outlier. I want to group the data points by Classification Criteria, Continent, and Fiscal Quarter. I understand how to do this for the whole data set, but once filters are applied the values are no longer correct.
My data structure consists of Estimation Accuracy (Value), a Key, Fiscal Quarters, and a Classification Criteria.
Table Name: Quotes
Quotes
Thus far I have created measures for Mean, Standard Deviation, Standard Error of the Mean, Upper Limit, Lower Limit, and Outlier.
However, I am not confident the measures are correct. I believe my problem is the Mean calculation, it's not grouping by the Fiscal Quarter and Continent.
Mean:
Mean =
VAR yy = FIRSTNONBLANK(Quotes[FiscalQtr],1)
VAR xx = GROUPBY(Quotes,Quotes[Continent])
RETURN
CALCULATE(
AVERAGE(Quotes[EstAccuracy]),
ALL(Quotes[FiscalQtr]),
Quotes[FiscalQtr] = yy,
Quotes[Continent] = xx)I would like the flexibility to change the filters on the data, example view mean by Classification Criteria for Q3 only. How can I write this into a measure so it is flexible enough?
Standard Deviation:
Std dev =
CALCULATE(
STDEV.P(Quotes[EstAccuracy]))Standard Error:
Std Error of the Mean = [Std dev] / SQRT(COUNTROWS(Quotes))
Confidence Interval
Confidence Interval = 1.96
Standard Error of the Mean:
Std Error of the Mean = [Std dev] / SQRT(COUNTROWS(Quotes))
Upper Limit:
Upper Limit_2 = [Mean] + ([Confidence Interval]*[Std Error of the Mean])
Lower Limit:
Lower Limit_2 = [Mean] - ([Confidence Interval]* [Std Error of the Mean])
Outlier Identification:
Outlier_2 =
IF(
AND(
FIRSTNONBLANK(Quotes[EstAccuracy],1) <= [Upper Limit_2],
FIRSTNONBLANK(Quotes[EstAccuracy],1) >= [Lower Limit_2]),
"Within",
"Outside"
)The Outlier attribute is NOT correct. Is there a better way to do this? I want the measures to hold when filters are applied. Measures not Correct
My ultimate goal is to have a table with all identified outliers present, I haven't been able to get this to work.
Any help is greatly appreciated!