Forum Discussion
Analysis from group data
I am working on a table similar to below-
| ID | Project Name | User | Activity | Field | User Input | Date |
| 1 | HCCD | John | A | Target Value | 5000 | 05-05-2024 |
| 1 | HCCD | John | A | Target Status | Over | 05-05-2024 |
| 1 | HCCD | John | A | Observation | No difficulty | 05-05-2024 |
| 1 | HCCD | Mark | B | Work Status | Complete | 06-05-2024 |
| 1 | HCCD | Mark | B | Quality of Work | Good | 06-05-2024 |
| 1 | HCCD | Laura | C | Overall Observation | Nice Team Work | 07-05-2024 |
| 1 | HCCD | Laura | C | Rating | 5 | 07-05-2024 |
| 2 | PCCB | Neil | A | Target Value | 6000 | 04-05-2024 |
| 2 | PCCB | Neil | A | Target Status | Over | 04-05-2024 |
| 2 | PCCB | Neil | A | Observation | Smooth Process | 04-05-2024 |
| 2 | PCCB | Mark | B | Work Status | Complete | 08-05-2024 |
| 2 | PCCB | Mark | B | Quality of Work | Poor | 08-05-2024 |
| 2 | PCCB | Laura | C | Overall Observation | Need to work on Report Quality | 15-05-2024 |
| 2 | PCCB | Laura | C | Rating | 3 | 15-05-2024 |
My objective is to create a user wise Remark column which averages User Answer in "Quality of Work" and "Rating" for all IDs to distill the results in following way-
| User | Laura's Rating | Mark's Rating |
| John | 4.5 | 4 |
| Neil | 5 | 3 |
I don't know if it can be acieved through UI functions or we need to use dax here. Pls assist.
EDIT: Adding some more context for better clarity, the output table is an illustration to show the desired output. Laura & Mark's rating is an average aggregation of multiple such IDs, two of which are indicated in first table. Also, Mark's "Quality of Work" can have User Input "very good/good/average/poor/not proper" which will be first converted to rating of 1 to 5 (5 being Very Good) and then aggregated as average in the output table. Also, Activity B & C are Assesor Activity.
Hi peterpan ,
Thanks for the reply from Ashish_Mathur and AllisonKennedy , please allow me to provide another insight:
1. Create a calculated column.
Quality of Work Rating = SWITCH( 'Table'[Field], "Quality of Work", SWITCH('Table'[User Input], "Very Good", 5, "Good", 4, "Average", 3, "Poor", 2, "Not Proper", 1), BLANK() )2. Create a calculated table.
Table 2 = UNION( SELECTCOLUMNS(FILTER('Table', 'Table'[Field] = "Rating"), "ID",'Table'[ID],"User", 'Table'[User], "Rating", 'Table'[User Input]), SELECTCOLUMNS(FILTER('Table', 'Table'[Field] = "Quality of Work"),"ID",'Table'[ID], "User", 'Table'[User], "Rating", 'Table'[Quality of Work Rating]) )3. Create a relationship.
4. Create a measure.
Measure = IF(MAX('Table'[Field]) = "Target Value",1)You can change the values in the matrix to the average you want.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
peterpan How do we know that Laura rated Neil 5 and John 4.5 ? Does your sample table on top match the results you're looking for in the distilled sample on the bottom?
There's a few ways you could approach this - either transform the data in Power Query (probably the best option) or use lots of DAX. Even with Power Query transformations you may still need some DAX.
I don't normally say this, but this might be a table where a 'Pivot' transformation on the 'Field' and 'UserInput' columns might be helpful. Then you can put the 'Rating' column in a matrix in the 'Values' and you can put the 'User' in 'Rows' and [Assessor] (this is the part I'm confused on - doesn't appear as those you have a field for this??) in 'Columns'
AllisonKennedy Laura's rating is the average of many more such ratings which of multiple IDs and below sample data is an illustration to show the desired output.
This first table is how the data is stored in database table and I am confused as to which way (power query or dax or something else) would be the most efficient in terms of processing time and perfromance. Maybe a mix of both. Eager to know how.
If I have to use Power query, I guess I'll have to add an identifier column based on activity to identify the Assesor User from the rest in order to pivot them into column.peterpan - so are activities 'B' and 'C' assessors?
AllisonKennedy Yes that's correct. Have added some more context in the Edits.
Hi,
Very confusing question. I see absolutely no connection between the input and output tables.
Hi Ashish_Mathur , Thanks for pointing that out. I have added some more context in Edits for better clarity.
I still do not understand your question. Someone who does will help.
Hi peterpan ,
Thanks for the reply from Ashish_Mathur and AllisonKennedy , please allow me to provide another insight:
1. Create a calculated column.
Quality of Work Rating = SWITCH( 'Table'[Field], "Quality of Work", SWITCH('Table'[User Input], "Very Good", 5, "Good", 4, "Average", 3, "Poor", 2, "Not Proper", 1), BLANK() )2. Create a calculated table.
Table 2 = UNION( SELECTCOLUMNS(FILTER('Table', 'Table'[Field] = "Rating"), "ID",'Table'[ID],"User", 'Table'[User], "Rating", 'Table'[User Input]), SELECTCOLUMNS(FILTER('Table', 'Table'[Field] = "Quality of Work"),"ID",'Table'[ID], "User", 'Table'[User], "Rating", 'Table'[Quality of Work Rating]) )3. Create a relationship.
4. Create a measure.
Measure = IF(MAX('Table'[Field]) = "Target Value",1)You can change the values in the matrix to the average you want.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous . Your solution seems to work well. Much appreciated.
