Average of one Column if 4 columns contain a value

[ashack]

Here's what I've got:

• Customer Survey Data Table:
  • Columns:
    • Instructor Name, 2nd Instructor, 3rd Instructor, 4th Instructor.
    • Overall Rating
• Rubric Data Table:
  • Column:
    • Instructor Name
• Monthly Report
  • Column:
    • Instructor Name

Now, there are other columns, but my issues is with these. I've created a table that will use the Instructor Name column to generate a list of unique Instructors. Obviously, Power BI has created this relationship on its own.

The conundrum that I find myself in is that in the first table, I have the 4 colums that will contain the same data. 

I need to be able to average the Overall Rating for 1 name if it appears in any of the 4 columns in the first table.

Example:

Course A:

Instructor Name: Billy Joe

2nd Instructor: Nancy Drew

3rd & 4th: Blank

Course B:

Instructor Name: Nancy Drew

2nd Instructor: Andrew Sparrow

3rd Instructor: Billy Joe

4th Instructor: Blank

I want to know Billy Joe's average from both courses, however the relationships are only looking at the Instructor Name column and isn't catching the other 3 colums and checking for the same name.

Been against this wall for a little over a week now...

Any help is appreciated!

[Phil_Seamark · Accepted solution]

Why not pivot your Customer Survey Data Table to be just two columns

Survey ID , Instructor , Rating

S1 , I1 , 5
S1 , I2 , 4
S1 , I3 , 5
S2 , I1 , 3

etc

This structure lends it self to tidier measures.  Thoughts?

[ashack]

@Phil_Seamark

That did it. I went and worked through some examples from the EdX Power BI course and figured out how to use Pivots. man.. I've been missing out!

Thanks for pointing this out. I've got my data now 🙂

[ashack]

@Phil_Seamark

I am all for tidier measures 🙂

Though, it sounds like I may need to spend some time learning about pivoting as I'm not sure how to accomplish what you've talked about. What you have in your example looks like something that would get me the results I'm looking for though!