Forum Discussion
Solved
Calculate Outlier For Multiple Data Sets In Table
Hello, I am fairly new to Power BI and need some help determining the best method to calculate outliers using IQR for many unique items in one transaction table ('Unfiltered Forecast') with transacti...
Please try this variation on your expression as a calculated column in your original table. You can then use that column as a filter in your other measures for analysis.
IsOutlier = VAR thisSKU = 'Unfiltered Forecast'[GroupSKU] VAR SKUValues = FILTER ( 'Unfiltered Forecast', 'Unfiltered Forecast'[GroupSKU] = thisSKU ) VAR LowerQuartile = PERCENTILEX.INC ( SKUValues, 'Unfiltered Forecast'[Total Quantity], .25 ) VAR UpperQuartile = PERCENTILEX.INC ( SKUValues, 'Unfiltered Forecast'[Total Quantity], .75 ) VAR InterQuartileRange = UpperQuartile - LowerQuartile VAR OutlierThresholdLower = LowerQuartile - InterQuartileRange * 1.5 VAR OutlierThresholdUpper = UpperQuartile + InterQuartileRange * 1.5 RETURN IF ( 'Unfiltered Forecast'[Total Quantity] >= OutlierThresholdUpper, "yes", "no" )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Please try this variation on your expression as a calculated column in your original table. You can then use that column as a filter in your other measures for analysis.
IsOutlier =
VAR thisSKU = 'Unfiltered Forecast'[GroupSKU]
VAR SKUValues =
FILTER ( 'Unfiltered Forecast', 'Unfiltered Forecast'[GroupSKU] = thisSKU )
VAR LowerQuartile =
PERCENTILEX.INC ( SKUValues, 'Unfiltered Forecast'[Total Quantity], .25 )
VAR UpperQuartile =
PERCENTILEX.INC ( SKUValues, 'Unfiltered Forecast'[Total Quantity], .75 )
VAR InterQuartileRange = UpperQuartile - LowerQuartile
VAR OutlierThresholdLower = LowerQuartile - InterQuartileRange * 1.5
VAR OutlierThresholdUpper = UpperQuartile + InterQuartileRange * 1.5
RETURN
IF (
'Unfiltered Forecast'[Total Quantity] >= OutlierThresholdUpper,
"yes",
"no"
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Pat, thanks so much! This worked perfectly. Great idea to put those into their own variables.