Forum Discussion
Filtering dimensions in a calculated column
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]))
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?
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!