Create Column Avg Group by

[aaron797]

Hi,

We have a survey, with a set number of questions, each question is assigned a "camp" they belong to. 

A person can be considered engaged in a camp if the average of their answers for the questions (for that camp) is greater than or equal to 3.

I am needing to get a measure that will tell me the number of engaged employees for each camp.

My approach would be to create a column, which is the average of answer, grouped by camp and ID, return IF AVG(ianswer) is >= 3," Engaged", else "Disengaged". However i am struggling with the correct syntax.

Currently, my data set looks like this,

UserID	QuestionID	Camp	Answer
1	1	1	3
1	2	1	4
1	3	2	2
1	4	2	4
2	1	1	4
2	2	1	4
2	3	2	2
2	4	2	1

My desired output is 

UserID	QuestionID	Camp	Answer	Camp Engaged
1	1	1	3	Engaged
1	2	1	4	Engaged
1	3	2	2	Engaged
1	4	2	4	Engaged
2	1	1	4	Engaged
2	2	1	4	Engaged
2	3	2	3	Disengaged
2	4	2	1	Disengaged

Would appreciate any tips for this!

[amitchandak · Accepted solution]

Try

Camp Engaged = if(AVERAGEX(FILTER('Table','Table'[UserID]=EARLIER('Table'[UserID]) && 'Table'[Camp]=EARLIER('Table'[Camp])),'Table'[Answer])>=3,"Engaged","Disengaged")

refer

https://www.dropbox.com/s/lr09ews92zhlka5/898625.pbix?dl=0

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 -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[aaron797]

Perfect, appreciated the PIBX file too.