This week’s tip explains how to make a calculated field in a pivot table and is a compulsory read for those of you who include metrics calculated from other fields (such as ROI or CTR) in your pivot table reports.
Pivot tables can create new columns of data that are functions of existing variables. If you create CTR (click through rate) or ROI (return on investment) in your raw data and then build a pivot table, you will come across issues, such as:
- Columns you create for CTR or ROI will simply sum the raw data and the totals will be incorrect
- Having extra variables in your raw data makes file sizes larger than they need to be
Instead you should always create these metrics as calculated fields once the pivot table is built.
We have some sample data showing monthly clicks and impressions for each search engine:
- Create a pivot table as normal
- Now we want to create a column showing click through rate…
- Click within the Pivot table and then click on the Options menu in the Ribbon
- Next select Formulas from the Tools section and choose Calculated Field
- Here you can name and create your click rate formula using the list of fields provided
- Bear in mind that the name cannot match the title of an existing field
- Click OK to finish!
- CTR will now appear in the table and you may just have to reformat the field quickly to show it as a percentage
- Don’t worry about the fact the column is labelled as a “Sum”. Individual click through rates have not been summed, they have been calculated
- Also, if you change your pivot table now, for example to show these metrics by month, the CTR will automatically update