Analysis from group data

[peterpan]

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.

[Anonymous · Accepted solution]

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.

[peterpan]

Thanks @Anonymous . Your solution seems to work well. Much appreciated. 

[Ashish_Mathur]

Hi,

Very confusing question.  I see absolutely no connection between the input and output tables.

[peterpan]

Hi @Ashish_Mathur , Thanks for pointing that out. I have added some more context in Edits for better clarity.

[Ashish_Mathur]

I still do not understand your question.  Someone who does will help.

[peterpan]

@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.

[AllisonKennedy]

@peterpan  - so are activities 'B' and 'C' assessors?

[peterpan]

@AllisonKennedy Yes that's correct. Have added some more context in the Edits.

[AllisonKennedy]

@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'