DAX calculation to exclude outlier values from an analysis

[Snook]

Hi,

I've consolidated a number of workbooks in Excel using Power Query and I'm looking to restrict the analysis to the data that falls within a certain parameter of the median value (exclude anything 50% below the median and 100% above, i.e. double) for each service reference. Is it possible using DAX to create a boolean column in the dataset that I can use as a filter criteria in a pivot table, e.g. TRUE include/FALSE exclude?

NB - I originally posted this query on an Excel forum that I'm a member of (Link) if anyone needs an example workbook.

Thanks in advance,

Snook

[Nathaniel_C]

Hi @Snook ,

Please try this for the DAX:

Median col = 

//var _column1 = MAX(MedianTable[Column1])

var _medianX = CALCULATE(MEDIAN(MedianTable[Column1]), All('MedianTable'))

var _calc = IF([column1] <.5* _medianX ||  [column1]>2*_medianX, "False", "True")

Return _calc

Or you could do it in Power Query, by running a Median() in Excel and filling a column which is imported with your other columns.  Once you have the value in Power Query, in my example, the column with the value "3" in every cell, you can add another column using the calculations - you then get the value in my column PowerQuery, and you can remove the column which has the "3" (Which I did not in the picture)
https://www.sumproduct.com/blog/article/power-pivot-principles/ppp-introducing-the-median-function

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Snook]

Thanks Nathaniel.

I'm trying to implement your solution, but it isn't returning the result I would expect. Against two of the service refs the min/max falls outside of what the defined parameter should be. Is the DAX code taking into account the service ref, i.e. calculating a median for each, when calculating whether the rate falls outside of the parameter?

I've calculated the median and related MIN/MAX in cell range F14 to I19. The pivot table in cell range F25 to I30 shows the analysed output using the DAX filter criteria.

Service ref A1 has a MAX rate (cell I26) that is greater than what the capped parameter is (cell I15). Likewise with the MIN rate for service ref A2.

Apologies if it's something simple I'm getting wrong, I'm very much a newcomer to DAX.

The rate of 8 against A2 is showing as TRUE, when the bottom of the median x 0.5 range should be 8.25 as per cell H16 (highlighted yellow)The A1 (Max) and A2 (Min) values are outside of the parameter range.

Regards,

Snook

[Nathaniel_C]

Hi @Snook ,
So you would like a median for each service ref? I don't think you mentioned that.  Would you do me a favor, - and it's good to do for any future questions - would copy and paste the data instead of a picture? Try it right on the page, but also try it </> using this selection from above.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Snook]

Hi @Nathaniel_C ,

Apologies for the confusion. Yes, I'm looking to create a median for each service reference and calculate the parameters for inclusion based on that.

This is the sample data I've been using (or you can download the workbook from the forum link in my opening post).

Service Ref	Rate
A3	£13.00
A4	£15.00
A4	£1.00
A4	£7.00
A4	£20.00
A3	£26.00
A4	£30.00
A3	£24.00
A5	£9.00
A4	£12.00
A1	£19.00
A2	£22.00
A3	£24.00
A5	£14.00
A5	£15.00
A2	£2.00
A1	£4.00
A5	£5.00
A1	£23.00
A5	£23.00
A3	£22.00
A3	£9.00
A2	£24.00
A4	£27.00
A1	£8.00
A2	£16.00
A2	£20.00
A5	£28.00
A3	£23.00
A3	£15.00
A4	£1.00
A5	£10.00
A2	£1.00
A2	£17.00
A1	£29.00
A3	£8.00
A2	£22.00
A5	£30.00
A2	£10.00
A3	£17.00
A2	£1.00
A3	£13.00
A2	£28.00
A4	£16.00
A4	£14.00
A1	£1.00
A1	£11.00
A2	£22.00
A5	£29.00
A4	£25.00
A2	£9.00
A5	£20.00
A3	£9.00
A3	£9.00
A1	£30.00
A4	£17.00
A4	£11.00
A1	£19.00
A1	£22.00
A1	£6.00
A2	£29.00
A5	£24.00
A3	£16.00
A2	£19.00
A3	£5.00
A4	£29.00
A1	£12.00
A1	£6.00
A5	£20.00
A2	£28.00
A1	£25.00
A2	£18.00
A1	£29.00
A1	£1.00
A4	£19.00
A5	£11.00
A5	£23.00
A4	£11.00
A2	£16.00
A4	£18.00
A1	£4.00
A5	£5.00
A4	£21.00
A2	£8.00
A2	£15.00
A5	£17.00
A5	£13.00
A5	£16.00
A3	£9.00
A4	£14.00
A5	£20.00
A5	£29.00
A3	£14.00
A5	£13.00
A4	£19.00
A5	£10.00
A5	£20.00
A3	£5.00
A1	£21.00
A4	£14.00
A2	£8.00
A5	£9.00
A2	£5.00
A5	£10.00
A2	£27.00
A4	£7.00
A2	£10.00
A1	£11.00
A3	£15.00

Regards,

Snook

[Nathaniel_C · Accepted solution]

Hi Snook,
Ran into some issues with the pound symbol, so split the column in Power Query and worked with the decimal. 

Median Column = 
var _serviceRef = MedianTable[Service Ref]

var _calc = CALCULATE(MEDIAN(MedianTable[Rate.2]),FILTER(ALL(MedianTable),MedianTable[Service Ref]=_serviceRef))

return If ([Rate.2]<_calc*.5 || [Rate.2]> _calc*2,"False","True")

See if this works. I also included a reference column to show the median number.  Please check that the calc is what you were looking for as the outlier.

Median Column for reference = 
var _serviceRef = MedianTable[Service Ref]

var _calc = CALCULATE(MEDIAN(MedianTable[Rate.2]),FILTER(ALL(MedianTable),MedianTable[Service Ref]=_serviceRef))

return _calc //If ([Rate.2]<_calc*.5 || [Rate.2]> _calc*2,"False","True")

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Snook]

@Nathaniel_C 

You've cracked it mate, that's fantastic, thank you! 😀

I've just checked my actual data and it's in decimal format, so it should be fine.

Thanks again, it's massively appreciated! 👍

Snook

[Nathaniel_C]

Hi @Snook 
You are welcome!
Nathaniel