Excel pivot tables Flashcards
What is a calculated field?
A calculated field adds new field within your Pivot Table. This new field adds certain calculations based, usually on the values of other fields.
- They use the sum of the underlying data of the fields the formula uses
Useful when you want to use ALL of the data from certain fields in your formulas
Excel example: if you want to estimate the cost of goods sold for each item and store
Assume that you can calculate the cost of goods sold as a percentage of the sales amount for each item and store. What is the calculation?
So you want to see what is the percentage of each item from the total sales amount in each store?
COGS = Total Sales x %
On the dial box, the formula that was used is
= ‘Sales Amount’*65%
What are 2 advantages of working with calculated fields on excel?
- Reduced risk of reference or calculations errors
- Increased scalability and flexibility
What is the difference between calculated items and calculated fields?
- Calculated items work with individual records from within a field
- Calculated fields work with all underlying data of the fields
When do you use a calculated field?
- When you want to work with all (sum) the underlying data of certain fields
- Adds a column to your source data
In contrast, a calculated item would add rows to your source data, not columns
What do you do if you want to add more than one calculated field?
After submitting the Add button on the dial box INSTEAD of the Ok button, repeat steps 3-5 as many times as necessary
How to add a calculated field to an existing pivot table
- Click on the pivot table you want to reference, anywhere on the table is fine
- Go to ribbon > analyze > fields, items & sets > calculated field
- Enter the name of the calculated field in the Name input box of the dialog box that pops up
- Enter the formula for the calculated field in the Formula input box
- Click add and/or OK
Now excel will add the calculated field with the values are of the pivot table and the field task pane
Syntax you cannot include when building formulas for calculated fields
- Call references
- Defined names
- The following types of worksheet functions:
- Functions that require call references or defined names. Ex: the Lookups (LOOKUP, HLOOKUP, and VLOOKUP and INDEX)
- Functions that return variable results, Ex: RAND, RANDBETWEEN, NOW and TODAY
- Database functions
- GETPIVOTDATA function
- Array functions
- Reference to Subtotals or Grand Totals of the Pivot Table
When calculating the field formula use the following formula
- Operators
- Expressions
- Certain functions, subject to the main limitations from the previous card (card 8)
- Constants
- References to fields. This includes both a. fields within the Pivot Table report and b. fields that aren’t currently displayed in the Pivot Table
Breakdown the formula used in the example.
=’Sales Amount’*65%
- ‘Sales Amount’: Reference to a field (Sales Amount) within the Pivot Table report
—If the name contains more than one word, number or symbol then wrap the name with single quotations. In the dial box, you will find all of the fields that are available for use in case you need help, then click insert field. Or you can double click the field you want, and it will be selected. - *: The multiplication operator
- 65%: A constant
Don’t forget we are assuming that we can calculate the cost of goods as a percentage of the sales amount
Excel then does the following:
Add the sales amount for each item (product) and store
This then results in the column titled Sum of Sales Amount
Steps to see all calculated fields within a Pivot Table
- Select Pivot Table
- Go to Ribbon > Analyze > Fields, Items & Sets > List formulas. Alternatively, use the keyboard shortcut “Alt, JT, J, L”
- Excel creates a new worksheet that has the following 2 tables: Calculated fields, calculated items.
Each of these tables displays the Solve Order, (Field or Item) Name, and Formula for each Calculated Field or Item
Note: When a cell is updated by more than 1 formula, the value is set by the formula with the last solve order. To change it for multiple calculated items or fields, on the options tab, in the calculations group, click fields, items & sets, and then click solve order
What is a common problem with calculated fields? Problems 1-2
They can only be summarized by the Sum. This is because calculated fields work wirht the Sum of the underlying data for a field.
You cannot use anything like count, average, max, min, product, count numbers, StdDevP, StDev, VarP, Var
Problem #2: calculated fields arent available when working with OLAP Data Sources or using the DATA Model
You can only use calculated fields or calculated items when you’re working with Pivot Table reports that aren’t based on an OLAP (online analytical processing) database.
Problems 3-4 What are other common calculated field problems?
- Calculation of subtotals and grand totals of calculated fields: These are calculated by taking the values of the subtotals or grand totals for the fields you refer to within the calculated field formula. It does not add up all of the numbers of the field you want it to, instead it applies the same equation that you wrote onto the “total” of the fields you selected within your formula
- Working with calculated fields generally clears the Undo Stack. The undo stack is completely cleared once you select OK within the dial box of the calculated field. You cannot undo previous actions
