This post follows from the previous post in this series of posts using a tiny made-up database that loosely mimics the Internet Movie Database that you can freely download. I am using Power Pivot in this example. I need Power Pivot because I am creating relationships between tables and creating measures.

I want to focus on the Average Rating. What does this mean? People (actors, directors, and so on) don’t have ratings in the database. Titles (movies, TV etc.) do have ratings. People are “known for” moving pictures. The real downloadable database online has at most four titles an individual can be known for. If look at all of the titles they are known for we can get an average rating. I created an Excel file and called it IMDB_Model_Tiny2.xlxs.

Calculate Function

To get it to work properly I had to create a measure that used the CALCULATE function. In Excel, click on the Power Pivot tab (menu). Click on Measures, New Measure.

We need to use the CALCULATE function.

=CALCULATE(AVERAGE(tbltitles[averageRating]),tblnametitleLink,tblnamebasics)

We need to get the AVERAGE of the averageRating column in the tbltitles table. To do that we need to run through the tables listed because in our pivot table we are starting with the name of a person from the tblnamebasics table and running through the tblnametitleLink table.

We could simplify the formula above. We could create a measure called Average Rating and substitute that into the CALCULATE formula above. Create a measure called Average Rating.

=AVERAGE(tbltitles[averageRating])