Changing the calculation of values
1. Changing the Calculation of Values
In the last chapter, we mentioned that the pivot table will typically default to a SUM or COUNT of the selected data. Let's take a look at the different options you can choose from.2. Examining the dataset for this chapter
In this chapter, we'll be using a dataset that contains a partial list of Films that were released between 2010 and 2016. These films are categorized by Year, Country, Language, and Certification. We'll be analyzing the Gross Revenues, Budgets, and maybe some other items as well.3. Changing the calculation of the Values field
Here is a pivot table created from that data, which shows the total Gross Revenue for each Certification. In the pivot table editor, let's go to the Values section. Right now, the pivot table is calculating the sum of Gross. As we discussed in the previous chapter, this means that the pivot table is adding up all of the different rows that meet the criteria shown in the table. For instance, if you add up all of the Gross Revenues for every PG-rated movie, you would get 13-point-2 billion dollars. You can change this by clicking on the arrow next to SUM, underneath the Summarize By header. You can see there are many options here. Let's walk through them.4. Using the COUNT calculation for values
Here is a pivot table showing the Gross Revenue for all G, PG, and PG-13 movies released each year. Right now it shows the SUM of all Gross Revenues, but if we change it to COUNT, then it will count how many movies had any amount of Gross Revenue. This shows us that 423 different PG-13 movies made some amount of revenue, compared to 155 PG movies, and 12 G-rated movies.5. Using the COUNTA calculation for values
One thing to remember, COUNT will show you how many total entries there are, but it only counts numbers! It shows how many numerical values exist in that column of your dataset. If we change the Values field to show Titles instead, which are text, then the COUNT will be zero. If we want to count titles, then we have to use COUNTA. COUNTA will count numbers, text, or basically anything that isn't blank. Now with COUNTA, we can see how many titles there are each year.6. Using the COUNTUNIQUE calculation for values
The last type of counting calculation is COUNTUNIQUE. This will count every unique entry for the given criteria. Let's count how many titles were released each year, and we can see the number is 656. If we look at the dataset, we can see there is a movie called Rio, and a movie called Rio 2. Let's pretend Rio 2 was also called Rio. Now if we look back at the pivot table, it only counts 655, since it excludes any duplicate titles.7. Other calculation options for values
Beyond COUNT, there are several other options that do pretty much just what they say. AVERAGE will give you the average of all values meeting the criteria. MAX and MIN give you the highest and lowest values, whereas MEDIAN will give you the middle value. PRODUCT will multiply the values, and finally, there are STANDARD DEVIATION and VARIANCE options if you need to do statistical analysis.8. Let's practice!
Let's practice.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.