I. Intro
You use formula fields to reference data in a base, enabling more sophisticated and in-depth data analysis.
II. Steps
Reference the corresponding data from a field
To obtain the data or content corresponding to each row, directly refer to a field in the current table in the formula field. The return value is a single data point or text of the corresponding record.
For example, we can reference the corresponding data from the first field (Field 1) in Table 1 by doing the following:
- Create a new formula field.
- Double-click any cell in the formula field to open the formula editor.
- Select the field (Field 1) to be referenced in the formula editor.
- Click Confirm.
250px|700px|reset
250px|700px|reset
Reference the entire dataset from a field
To obtain all the data or content from a field, you need to refer to a table and field in the formula field. The return value is all the data or text in the referenced field.
For example, we can reference all the data of the first field (Field 1) in Table 1 by doing the following:
- Create a new formula field.
- Double-click any cell in the formula field to open the formula editor.
- Select the table (Table 1) and field (Field 1) to be referenced in the formula editor.
- Click Confirm.
250px|700px|reset
250px|700px|reset
Reference datasets across tables
To obtain all the data or content from a field in another table, you need to refer to the target table and field in the formula field. The return value is all the data or text in the referenced field in the other table.
For example, we can reference all the data of the first field (Field 1) in another table (Table 2) by doing the following:
- Create a new formula field.
- Double-click any cell in the formula field to open the formula editor.
- Select the table (Table 2) and field (Field 1) to be referenced in the formula editor.
- Click Confirm.
III. Use cases
Reference corresponding data to find the highest value of the record
Scenario: Calculate the highest sales volume of three products every month in the Team A Sales Info table.
Formula: MAX([Computer Sales],[Air-conditioner Sales],[Refrigerator Sales])
250px|700px|reset
250px|700px|reset
Reference all data to find the difference between the highest value and the current value
Scenario: Calculate the difference between the highest computer sales volume of the year and the computer sales volume of each month in the Team A Sales Info table.
Formula: MAX([Team A Sales Info].[Computer Sales])-[Computer Sales]
250px|700px|reset
250px|700px|reset
Reference all data across tables to find the difference between the highest value in Team B and the current value
Scenario: Calculate the difference between the monthly computer sales volume of Team A in the Team A Sales Info table and the highest computer sales of Team B.
Formula: MAX([Team B Sales Info].[Computer Sales])-[Computer Sales]
250px|700px|reset
250px|700px|reset
250px|700px|reset
Reference the data set in the current table and calculate the proportion
Scenario: Calculate the proportion of Team A's computer sales volume in a month relatived to the total sales in the first half of the year (Jan-June).
Formula: [Computer Sales]/SUM([Team A Sales Info].[Computer Sales])
Note: Double-click the formula field and change the field format to percentage.
250px|700px|reset
250px|700px|reset