Reply
Frequent Visitor
Posts: 7
Registered: ‎01-12-2017
Accepted Solution

Average of one Column if 4 columns contain a value

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!


Accepted Solutions
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Average of one Column if 4 columns contain a value

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?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post


All Replies
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Average of one Column if 4 columns contain a value

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?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Frequent Visitor
Posts: 7
Registered: ‎01-12-2017

Re: Average of one Column if 4 columns contain a value

@Phil_Seamark

 

 

I am all for tidier measures Smiley Happy

 

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!

Highlighted
Frequent Visitor
Posts: 7
Registered: ‎01-12-2017

Re: Average of one Column if 4 columns contain a value

@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 Smiley Happy