How to identify values outside of averages?

[Anonymous]

Hi,

My aim is to identify data points where the value for a store is outside of the store's average value for that weekday by + or - 20%

For example, if store 1 has a Monday average of 300 and on one particular Monday they have a value of 500, I would like to see this data point as it could be flagged as a potential outlier for investigation.

A basic sample of the data struture is given below:

Date	Store	Value
1/01/2020	1	200
1/01/2020	2	100
1/01/2020	3	300
2/01/2020	1	200
2/01/2020	2	100
2/01/2020	3	20
3/01/2020	1	300
3/01/2020	2	200
3/01/2020	3	100
4/01/2020	1	100
4/01/2020	2	100
4/01/2020	3	25
5/01/2020	1	400
5/01/2020	2	300
5/01/2020	3	1000
6/01/2020	1	200
6/01/2020	2	100
6/01/2020	3	40
7/01/2020	1	0
7/01/2020	2	1000
7/01/2020	3	20

How should I go about creating a measure and visualisaing this?

[V-pazhen-msft · Accepted solution]

@Anonymous 
The 3 tables should have linked together. 

1. Create a Weekday column in Date Table

Weekday = WEEKDAY(DateTable [Date],2)

2. The measure can be something like:

Outlier = 
Var AvgStoreWeekday= calculate(Average(DataTable[Value]), filter(all(storetable),[storename]=max([storename])),filter(all(datetable),[weekday]=max([weekday]))
Var diffpercent= (Diff = Sum(DataTable[Value]) - [AvgStoreWeekday]) / [AvgStoreWeekday]

Return  IF([diffpercent]<-0.2 || [diffpercent]>0.2), Sum(DataTable[Value]), 0)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[V-pazhen-msft · Accepted solution]

@Anonymous 
The 3 tables should have linked together. 

1. Create a Weekday column in Date Table

Weekday = WEEKDAY(DateTable [Date],2)

2. The measure can be something like:

Outlier = 
Var AvgStoreWeekday= calculate(Average(DataTable[Value]), filter(all(storetable),[storename]=max([storename])),filter(all(datetable),[weekday]=max([weekday]))
Var diffpercent= (Diff = Sum(DataTable[Value]) - [AvgStoreWeekday]) / [AvgStoreWeekday]

Return  IF([diffpercent]<-0.2 || [diffpercent]>0.2), Sum(DataTable[Value]), 0)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a weekday column

Weekday = WEEKDAY([Date],2)

Avg Store Weekday= calculate(Average(Table[Value]), Allexcept(Table,Table[Store],Table[Weekday]))

You can take Diff with this Avg

Diff = Sum(Table[Value]) - [Avg Store Weekday]

[Anonymous]

This almost worked. Assuming I have 3 tables - Date, Store, Data ; How do I adjust the formula to refer to the Weekday column from my date table and the Store Name from my Store table?