Using Pivot Table Filters and Calculated Fields for Deeper Analysis

1. Analytical Context

In the previous step of the analysis:

• A pivot table summarized total revenue by year.
• Average revenue per movie was calculated.
• 2015 showed unusually low average revenue.
• 2015 also had the highest number of movies released.

Hypothesis:

The lower average revenue in 2015 may be due to a higher proportion of low-performing movies (less than $10 million revenue).

To test this hypothesis, we use:

• Pivot table filters
• Calculated fields
• Cross-table percentage calculations

---

2. Applying Filters in a Pivot Table

Purpose of Filtering

Filtering allows analysts to:

• Narrow data to specific conditions.
• Focus analysis on relevant subsets.
• Test hypotheses quickly.

---

Step: Filter Movies Under $10 Million

1. Select a cell in the copied pivot table.
2. Add a filter to the Box Office Revenue column.
3. Choose “Filter by condition.”
4. Set condition:
   • Less than → $10,000,000.

This filters the pivot table to include only low-revenue movies.

---

Observation

Result shows:

• 20 movies in 2015 earned less than $10 million.
• This appears higher than other years.

However, since 2015 had more total movies, we must evaluate proportion, not just raw count.

---

3. Verifying Average Revenue Using a Calculated Field

Because this pivot table was copied and filtered, the average must be revalidated.

What Is a Calculated Field?

A calculated field:

• Adds a custom formula inside a pivot table.
• Operates on aggregated fields.
• Uses other pivot fields in its formula.

---

Example Formula

SUM(Box Office Revenue) / COUNT(Box Office Revenue)

Because the table is filtered:

• SUM includes only movies under $10M.
• COUNT includes only movies under $10M.

This confirms the filtered average revenue is accurate.

Verification ensures analytical integrity.

---

4. Calculating Percentage of Low-Revenue Movies

To compare across years, we compute:

(Number of movies under $10M) 
÷ 
(Total number of movies per year)

Instead of using a calculated field, we use a standard formula referencing:

• Filtered pivot table
• Original pivot table

---

Steps

1. Create new column: “Percent of Total Movies.”
2. Divide:
   • Filtered count (low-revenue movies)
   • Original count (total movies per year)
3. Drag formula down.
4. Format as percentage.

---

5. Interpretation of Results

Results show:

• 16% of 2015 movies earned less than $10 million.
• Other years are near 10%.

This supports the hypothesis:

• 2015 had a higher share of low-performing movies.
• This likely lowered the average revenue for that year.

---

6. Why Percentages Matter

Raw counts can mislead when:

• Sample sizes differ.
• Total production volume varies.

Percentages normalize comparisons.

Example:

• 20 low-revenue movies in a year with 125 releases is different from 20 in a year with 80 releases.

Percentages provide proportional insight.

---

7. Analytical Process Demonstrated

1. Identify anomaly (low average in 2015).
2. Form hypothesis.
3. Filter subset of data.
4. Validate calculations.
5. Compute proportional metric.
6. Compare across groups.
7. Interpret findings.

This reflects real-world analytical workflow.

---

8. Pivot Table Strengths Demonstrated

Pivot tables allow:

• Aggregation (Sum, Average, Count)
• Filtering by condition
• Dynamic recalculation
• Calculated fields
• Quick exploratory testing

They reduce need for manual formulas.

---

9. When to Go Further

In real business analysis, next steps might include:

• Analyzing genres in 2015
• Studying marketing budgets
• Comparing production costs
• Examining audience ratings
• Identifying distribution differences

Data rarely answers everything in one step.

---

10. Key Takeaways

• Filtering helps isolate subsets.
• Calculated fields verify metrics within pivot tables.
• Percentage calculations normalize comparisons.
• Pivot tables are powerful for hypothesis testing.
• Always validate averages after filtering.
• Proportion often reveals deeper insight than totals.

---

Final Summary

By combining:

• Pivot tables
• Filters
• Calculated fields
• Cross-table formulas

We tested a hypothesis and identified a plausible explanation for 2015’s lower average revenue.

Pivot tables are not just organizational tools—they are powerful analytical engines for structured data exploration and validation.