How to compare individual series values with lower and upper limit measures (anomaly detection)?

[hedgy123]

Hi,

   I have a dataset that contains time series data - sales as a function of time by product and customer.  It looks kind of like this:

Customer	Product	Date	Sales
1	X	2/1/21	24
1	X	2/2/21	56
1	Z	2/3/21	32
2	Y	2/2/21	542
2	Y	2/3/21	23

   I'd like to flag anomalous sales point that are too large or too low for a given customer for a given product.   So I created measures like this:

  QUARTILE1 = PERCENTILEX.INC(SUMMARIZE(Table,'Table'[Customer],'Table'[Product]),CALCULATE(PERCENTILE.INC('Table'[Sales],0.25)),0.25) 

QUARTILE3 = PERCENTILEX.INC(SUMMARIZE(Table,'Table'[Customer],'Table'[Product]),CALCULATE(PERCENTILE.INC('Table'[Sales],0.75)),0.75)

IQR = [QUARTILE3]-[QUARTILE1]

LOWER_LIMIT = [QUARTILE1]-1.5*[IQR]

UPPER_LIMIT = [QUARTILE3]+1.5*[IQR]

   and now I'd like to compare each individual Sales value with the [LOWER_LIMIT] and [UPPER_LIMIT] for a given customer and a given product.  I can't seem to figure it out.  If I just do a calculated column that compares Table[Sales] with the limits, I always get the same value.  

     Could someone please help?  

[MFelix]

Hi @hedgy123 ,

You cannot  create a calculated column based on a measure. Measures are based on context so the result is variable depending on the values you have on the context, meaning visualizations, filters, and so on.

You need to make this based on the values you present you need to apply ALL or ALLSELECTED on the quartile value that will get the same value for every single row then use that has your difference to each of the row value.

Can you share based on the values you present what are the values for the quartiles that you need to achieve?

[hedgy123]

Thanks Miguel.  Yes, here's what I'd like to achieve with some sample data:

Here's my starting data table:

Starting data

Here's a calculation of the quartiles and upper and lower limits per customer per product

and here's my desired output: I compare every Sales value with the upper and lower limits for the relevant customer and product, and flag those values that are either lower than the lower limit or larger than the upper limit as outliers:

It's this last step I am struggling with.   I am a PowerBI beginner, so not sure what you mean by applying ALL or ALLSELECTED on the quartile value?

Thanks very much for your help.