skip to Main Content

 
In the voting from the last post, the majority of respondents found the third ranking to be most realistic:

image

I agree – this is the one that I personally found most consistent with reality.  “The Exorcist” surprises me – I did not expect to see it in the top ten – but it was actually higher in the other two rankings.

Mashup of Movie Grosses with Average Historical Ticket Prices

Data Set #1:  Raw Movie Grosses

image

Raw Movie Grosses
(Source:  The-Numbers.com – Click Image for Source Data)

Data Set #2:  Average Historical Ticket Prices

image

Average Historical Ticket Prices
(Source:  BoxOfficeMojo – Click Image for Source Data)

Relate both of these to the Years table:

image

Related the Two Mashup Sets to My Common “Years” Table
(Yes, I Have Many Data Sets Now)

The Measures

Yes, it’s overkill in this case to use measures.  I could just use a calc column.  In fact I probably should.  But I have a thing for measures, they just feel more “portable” over the long haul.

[US Movie Gross] =
   MAX(MovieGrossesUS[Total Box Office])

[Avg Movie Ticket Price] =
   AVERAGE(MovieTicketPrices[Avg  Price])

[Tickets Sold Approx] =
   [US Movie Gross]/[Avg Movie Ticket Price]

Flattened Pivot

One of my favorite techniques, the flattened pivot!

image

Yields…

image

Turn Off Subtotals

To get a truly flat pivot, you then have to turn off subtotals:

image

And that yields:

image

Download the Workbook!

Go ahead and grab the whole thing here:

https://powerpivotpro.com/wp-content/uploads/2012/04/ShareableMovies.xlsx 

(This only contains movies and ticket prices though – none of my other goodies like Inflation etc.)

What were the other two rankings?

They were both based on differing measures of inflation.  If I have time tomorrow, I may do a bonus post.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

Leave a Comment or Question