Forum Discussion
Get Average based on several categories
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
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
2 Replies
- amitchandak
Super User
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 LinkedinThanks amitchandak . That worked an absolute treat!
