Filtering dimensions in a calculated column

[smoortema]

I have a table with prices, for shops, products and dates, and I have a separate dimension table for each dimension. I would like to identify erroneous data in the price column by searching for outliers - values that lie far out of the distribution of the data for a given date, article and shop type combination. To do this, I would need to see the first quartile and third quartile of the distribution in two calculated columns using the PERCENTILEX function, where the values are for the date and article and shop type come from the given row, but all the shops within that type are considered (and there are also other columns in the table).

I have learned that CALCULATE is the best practice in these cases but I did not succeed to produce results that work this way:

PriceFirstQuartile =

CALCULATE(

    PERCENTILEX.INC('Price','Price'[Price], 0.25),

    ALL('Price'),

    'Price'[ARTICLE_CODE],

    'Price'[DATE],

    'Price'[SHOP_TYPE_CODE]

)

I have only found two ways to make this work, both are with variables: 

Version 1:

PriceFirstQuartile v1 =

VAR MyArticle = 'Price'[ARTICLE_REF]

VAR MyDate = 'Price'[Date]

VAR MyShopType= 'Price'[SHOP_TYPE_CODE]

VAR MyTable =

FILTER(

    'Price',

    'Price'[ARTICLE_CODE] = MyArticle && 'Price'[Date] = MyDate && 'Price'[SHOP_TYPE_CODE] = MyShopType

)

RETURN

PERCENTILEX.INC(MyTable,'Price'[Price], 0.25)

Version 2:

PriceFirstQuartile v2 =

VAR MyArticle = 'Price'[ARTICLE_CODE]

VAR MyDate = 'Price'[Date]

VAR MyShopType= 'Price'[SHOP_TYPE_CODE]

VAR MyTable =

CALCULATETABLE(

    ALL('Price'),

    'Price'[ARTICLE_REF] = MyArticle, 'Price'[Date] = MyDate, 'Price'[SHOP_TYPE_CODE] = MyShopType

)

RETURN

PERCENTILEX.INC(MyTable,'Price'[Price], 0.25)

My question is: how to make it work with CALCULATE and what is the reason that my proposed suggestion did not work out?

[AllisonKennedy · Accepted solution]

@smoortema  Why are you creating this as a calculated column?

Be really careful using aggregate functions within calculated columns - it does not provide the right context and is possibly what you mean when you say 'I have learned that CALCULATE is the best practice in these cases'

CALCULATE puts you into a filter context, so converts the calculated column into a single measure, taking the current row values as the filter context for that measure. Since it puts you into an equivalent filter context, your column references will behave differently than you might expect. 

If I understand your question properly (again still not sure why you're doing this as a column and not a measure), you could try:

PriceFirstQuartile =

CALCULATE(

    PERCENTILEX.INC('Price','Price'[Price], 0.25),

    ALLEXCEPT('Price',

    'Price'[ARTICLE_CODE],

    'Price'[DATE],

    'Price'[SHOP_TYPE_CODE]

     )

)

[smoortema]

Thanks @AllisonKennedy, your suggested solution works!

The reason that I put it in a calculated column because I would like to identify (and then exclude) outliers in the table. Outliers are defined as rows for which:

Price > ThirdQuartile + 3 * (ThirdQuartile - FirstQuartile)

or

Price < FirstQuartile - 3 * (ThirdQuartile - FirstQuartile)

So basically these are values that fall very far out of the normal range of prices for that given day, article and shop type. They are probably there because of an error made by the person who was typing in the values.

Once I have the outliers, I create a calculated table where i filter out all outlier values, and I continue the rest of my calculations on that table.

For the beginning, to analyse, it was good to see all the values in the same table, besides each other so calculated columns were useful. Are you saying that all this putlier filtering could be done only by using measures? 

[AllisonKennedy]

@smoortema Yes, all this can be done using measures instead of columns, since you're aggregating the data. It will be easier and more efficient to load too!