Setting a benchmark for identifying outliers

DAX DAX Calculations
1

Hi Everyone,

I am bit embarrasaed with this request as I think I am missing something basic.

From daily sales data, about half a dozen days stand out as anomolous. This is confirmed by the outliers chart from the analytics tool. I want to create a benchmark measure to help my understanding of DAX but more inportantly so I can bring in other measures in a table which expains the variance from a number of averages.

In the attached file I have set up two Outlier measures, one with a benchmark of 85000 and the other being 1.5 times the 28 day average.

I assume one of the measures will look like
Outliers =
VAR Base = [Sales]
VAR Benchmark = 85000

Return
CALCULATE([Sales], FILTER(Sales, [Sales] >Benchmark))

and the other
Outliers 2 =
VAR Base = [Sales]
VAR Movavg = [Sales 28 Day Rolling Avg]
VAR Multiple = 1.5
VAR Benchmark = Movavg * Multiple

Return
CALCULATE([Sales], FILTER(Sales, [Sales] >Benchmark))

But they don’t work.

The file is attached for review and the relevant page is called Outliers and the measures are Outliers and Outliers2

Your help will be greatly appreciated.

Outliers.pbix (2.0 MB)
Thanks
Michael Gordon

2

Hi @MIchaelGordon,

Thanks for providing a sample file :+1:

By iterating over Sales you are evaluating your Outliers calculation row by row instead of day by day. I would also suggest creating a [Benchmark value] measure, so when that changes you only have to update it once. Anyway try something like this.

Outliers v2 = 
VAR  Benchmark = [Benchmark value]
RETURN

CALCULATE( [Sales], FILTER( VALUES( 'Calendar'[Date] ), [Sales] >Benchmark))

image
.

Same applies to your [Outliers 2] measure, your avg looks fine to me, just change the calculation from row by row over Sales to day by day over your Calendar table…

I hope this is helpful.

1 Like
3

Melissa, Thank you very much (and I have followed your suggestion and created a Benchmark Value measure) But I have one follow up query.

The measure is based on being greater than or equal to 1.5 times the 28 day moving average. (I changed the original formula to include equal to). However, it is not picking up instances when the ratio would be expected to round up to 1.5. You can see the problem in the table below.

image

This is the measure
Outliers2 =
VAR Base = [Sales]
VAR Movavg = [Sales 28 Day Rolling Avg]
VAR Multiple = 1.5
VAR Benchmark = Movavg * Multiple

Return
CALCULATE([Sales], FILTER( VALUES( ‘Calendar’[Date] ),[Sales] >=Benchmark))

Thanks again Michael Gordon

4

That is true, if you want that to be taken into account add the ROUND function to your calculation and specify the number of digits.

I hope this is helpful.

5

Melissa,

Thank you for your help. Much appreciated.

Have a great day

Michael Gordon

1 Like