1

I have a question concerning calculated fields in Excel Pivot Table.

I have attached an image for perusal.

Observed that in the attached image below, what happened was that I tried to find out using the calculated field in pivot table, the Average Profit Per Genre.

I have summarized both the Sum Of Budget and Sum Of Box Office Revenue to Average Of Budget and Average Of Box Office Revenue respectively.

However, it seems that calculated field won't allow me to minus the Average Of Budget and Average Of Box Office Revenue, so that I can get the Average Profit Per Genre.

It seems that calculated field would only calculate the Sum Of Budget and Sum Of Box Office Revenue.

Would like to ask if there are any other way around this? So that i can find out the Average Profit Per Genre?

Thanks!

enter image description here

Improve this question

2 Answers 2

Reset to default
1

Calculated Fields always sum fields, no matter what aggregation you set via the Value Field Settings dialog box.

You can either add your table to the Data Model, i.e. use Power Pivot, or else add a dummy column to your table - named 'Dummy', say - which contains the value 1 in every row. You can then use the following Calculated Field:

= ('Box Office Revenue' - Budget) / Dummy

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I see the Excel Pivot Table you are trying to create is from the Hands-On Activity in the Google Professional Certificate in Data Analytics - Course 5.

I have a workaround to solve the problem, though it does not directly address the Calculated Fields issue.

STEP ONE: As Pivot Table will not allow you to add a column, start typing in Cell D3, the heading of the column as "Average Profit". In Cell D4, type the formula =C4-D4 (which calculates Average Box Office minus Average Budget = Average Profit). Then fill down from Cell D4 to get Average Profit for each genre.

STEP TWO: Select the Pivot Table along with column D. Copy and go to cell F3 and click paste. Now, select the range of cells where your data is, from F3 to I20. Go to Sort & Filter and choose "Custom Sort". Make sure "My data has headers" is checked.

  • SORT BY - Average Profit
  • SORT ON - Values (leave as default, no need to change)
  • ORDER - Largest to smallest

You will then find all the genres arranged by Average Profit, which will help you answer the activity's requirement - "Which movie genre generates the most profit on average?"

Hope this helps. Feel free to ask for any clarifications. Thank you and keep going, guys!

P.S. I thought of adding a screenshot, but that might be considered a breach of Coursera Honor Code, as it would have the answer to the activity's requirement!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.