Power BI Matrix Table with Customized Columns and Rows

[Anonymous]

I have a table taken from Tableau (see sample data below). I have tried to replicate this using a table and amatrix table but neither worked. Matrix table would not work because I still need the columns all displayed (Job Posting Title, Hiring Manager, Candidate Name, Source, Applied Date). 

The main issue is to list Interviewer as column and the Average Interview Rating from all the interviewers Req ID for each candidate (row). 

							Interviewer
Req ID	Job Posting Title	Hiring Manager	Candidate Name	Source	Applied Date	Average Interview Rating	Amy White	Amanda Smith	Colin Wong
A1234	Software Engineer	Amy White	Joe Smith	Indeed	1/1/2024	3	3	3	3
A1234	Software Engineer	Amy White	Sandy Soul	Linkedin	3/1/2024	2.5	3		2

Any help would be greatly appreciated! 

[Anonymous]

Hi @Anonymous ,

Please try:

Create a custom column in Power Query:

Table.AddColumn(#"Changed Type", "Average", each List.Average(List.Select({[Amy White], [Amanda Smith], [Colin Wong]}, each _ <> 0)), type number )

Click Table.

Select the three columns for unpivot.

Exit Power Query and place the fields as shown below:

Set up the matrix as shown below:

The final page result is shown below:

The pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hi there,

Thank you for providing a solution. I was not being clear in my original post, but the source data looks like this:

Req ID	Job Posting Title	Hiring Manager	Candidate Name	Source	Applied Date	Interviewer	Rating
A1234	Software Engineer	Amy White	Joe Smith	Indeed	1/1/2024	Amy White	3
						Amanda Smith	3
						Colin Wong	3
A1234	Software Engineer	Amy White	Sandy Soul	Linkedin	3/1/2024	Amy White	3
						Amanda Smith
						Colin Wong	2

And the table visual I'm trying to build is this:

						Interviewer
Req ID	Job Posting Title	Hiring Manager	Candidate Name	Source	Applied Date	Average Interview Rating	Amy White	Amanda Smith	Colin Wong
A1234	Software Engineer	Amy White	Joe Smith	Indeed	1/1/2024	3	3	3	3
A1234	Software Engineer	Amy White	Sandy Soul	Linkedin	3/1/2024	2.5	3		2

I think the process would be different in Power Query?