We know that pivot tables are useful to segregate and group data in a meaningful way so we can identify insights and generate new questions and answers for the data.
Let's take an example to understand in a simple way how it is done
We are given a data for movies.
We are given some basic questions by stakeholders to start with the analysis.
- How much revenue generated each year from movies?
- What is the average revenue per movie each year?
That's a good direction for a start. Let's create a pivot table to find out answers.
2015 has the lowest average revenue per movie...why?
Now we will focus on 2015 data.
It is evident that 2015 had released higher number of movies (124) than any other year. Hence an obvious thought would be that it reduced the average because of that.
But is it possible that it has higher number of movies with low box office collection?
We don't know yet so let's try to find that out.
I took $10 million as a round figure from the box office revenue column to find out how many movies in out list had collection of less than $10 million...
Why I took $10 million limit for filtering data?
I can ask the stake holder to quantify what they consider as low revenue movie based on industry standard. Or (as I did in this case) I figured out that all movies less than $10 million revenue were breakeven for investors.
Meaning, average budget for movies with revenue less than $10 million was approx. $10 million. So investors did not make any money if revenue was less than $10 million.
That's why I took $10 million as a round figure to establish a movie as low revenue movie.
Using filter function in pivot table, I have filtered count of all movies with less than $10 million revenue.
Now with the support of data, I can say with confidence that 2015 had higher percentage (16.13%) of low revenue movies and that's why average revenue per movie was lowest in that year.
No comments:
Post a Comment