Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power BI Matrix Table with Customized Columns and Rows

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 IDJob Posting TitleHiring ManagerCandidate NameSourceApplied DateAverage Interview RatingAmy WhiteAmanda SmithColin Wong
A1234Software EngineerAmy WhiteJoe SmithIndeed1/1/20243333
A1234Software EngineerAmy WhiteSandy SoulLinkedin3/1/20242.53 2

 

Any help would be greatly appreciated! 

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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 IDJob Posting TitleHiring ManagerCandidate NameSourceApplied DateInterviewerRating
      A1234Software EngineerAmy WhiteJoe SmithIndeed1/1/2024Amy White3
            Amanda Smith3
            Colin Wong3
      A1234Software EngineerAmy WhiteSandy SoulLinkedin3/1/2024Amy White3
            Amanda Smith 
            Colin Wong2

       

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

            Interviewer
      Req IDJob Posting TitleHiring ManagerCandidate NameSourceApplied DateAverage Interview RatingAmy WhiteAmanda SmithColin Wong
      A1234Software EngineerAmy WhiteJoe SmithIndeed1/1/20243333
      A1234Software EngineerAmy WhiteSandy SoulLinkedin3/1/20242.53 2

       

      I think the process would be different in Power Query?