Forum Discussion
Create Column Avg Group by
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!
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
2 Replies
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Perfect, appreciated the PIBX file too.
