Get Average based on several categories

[topcat777]

Hi

Hoping you guys can help me. Searched the internet for a soloution but at a loss.

I have some data like the below that lets me know the time work has taken. I know anything over 10 in the time column is an error and I want to replace this with the average. So on row 4 I would want to return the average of all Emma's UK Build jobs, excluding the outliers. So in the case, the average or the first 3 rows, as they meet the criteria (Name= Emma, Area = UK, Task = Build, Time <10).

Row 8 would also be replaced with 1.5, an average of rows 6 and 7 that meet Emma/UK/Repair/<10. 

Job Number	Name	Area	Task	Time
1	Emma	UK	Build	2
2	Emma	UK	Build	3
3	Emma	UK	Build	4
4	Emma	UK	Build	18
5	Emma	USA	Build	1
6	Emma	UK	Repair	2
7	Emma	UK	Repair	1
8	Emma	UK	Repair	11
9	Dave	UK	Repair	5
10	Dave	USA	Repair	1

I don't know if this is the best way to do it but I was going to add a column that returned the Time if it was less than 10 but replaced it with the average if it was >=10. For the life of me I just can't figure out how to return that average when I have 3 catagories to match to.

I hope you guys can help me.

Thanks in advance.

Thom

[amitchandak · Accepted solution]

Create a new column like. Link :https://www.dropbox.com/s/2uioqvtxghzr0uo/AverageBaedonCategory.pbix?dl=0

Avg Time = 
var _newavg =AVERAGEX(filter(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name]) && Sheet1[Area]=EARLIER(Sheet1[Area]) && Sheet1[Task]=EARLIER(Sheet1[Task]) && Sheet1[Time]<10),Sheet1[Time])
return
if(Sheet1[Time]<10,Sheet1[Time],_newavg)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[topcat777]

Thanks @amitchandak . That worked an absolute treat!