Adding a Calculated Field to a Dataset

Published on October 5, 2016

Where do we need calculated fields?

Calculated fields are used when you need to aggregate or manipulate our data in some way. For example, let’s look at the dataset Employee_Salary_2014 [Data source]. This dataset shows employee salaries in the Montgomery County, Maryland in the year 2014. It has fields showing the Current Annual Salary and the Overtime Pay received by each employee. To create a visualization Net Salary received by each employee, I’ll show you how to add a calculated field that calculates the net salary for each row, by adding the values in the Overtime Pay field to the values in the Current Annual Salary field.

In this exercise, I’ll show how to:

Manage dataset field attributes

Derive new columns in an existing dataset

Create and refine a visual that contains the new column

Prerequisites

You’ll need access to the dataset Employee_Salary_2014 [data source]. If you haven’t created this dataset yet, see these links below to:

Convert Dimensions into Measures

Sometimes imported data does not naturally divide into dimensions and measures. In such a scenario, you can declare and manipulate the dataset by converting Dimensions into Measures and vice versa (this is a toggle function).

Convert Dimensions into Measures by clicking the Dim in the left of the rows you want to convert to measures. Make sure you set the field type to Integer:

Create a new calculated field in the dataset

Click Edit Attributes. The Dimensions and Measures lists appear.

In the Measures list, find the field current_annual_salary.

Click Clone next to the field name. A new field named Copy of current_annual_salary appears below the current_annual_salary row.

In the Expression field, enter the following expression, and click Validate.[current_annual_salary] + [2014_overtime_pay] A confirmation message appears.

Click Apply.

Note that the new measure Net Salary Received 2014 appears in the measures list. image

Click Save

To use the new column in a visualization

Click New Visual at the top right of the main screen. A new table appears, showing the calculated field Net Salary Received 2014. Optionally, you can adjust the visual by editing the table header row and cell format.

To learn how edit the field names, or table header row, follow the instructions in Alias. Here, I made the following changes:

Field gender has alias Gender.

Field current_annual_salary has alias Annual Salary 2014.

Field 2014_gross_pay_received has alias Gross Pay Received 2014.

Field 2014_overtime_pay has alias Overtime Pay 2014.

Field department has alias Department.

To format the cells in the data table, follow the steps mentioned here.