Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashack
Frequent Visitor

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!

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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

3 REPLIES 3
Phil_Seamark
Employee
Employee

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!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.