Overview
Calculate a Key Value with custom calculations in Data Explorer. When calculating a Key Value, you can enter complex formulas referencing the columns in your transactional table of data. These calculations are dynamic, when you change the date range of your Key Value, the expressions is re-run and the correct value is displayed. You can also leverage Comparison Periods to see performance versus a previous time period.
Expressions in Calculated Key Values follow a Google Sheet like syntax for aggregations and mathematical operators. For example, if you wanted to calculate Average Deal Size, you would enter this expression:
SUM([Deal Amount])/COUNT([id])
You can also leverage conditional aggregations like SUMIFS and COUNTIFS, so that you can create advanced calculations without SQL knowledge.
How to Add a Calculated Key Value
Calculated Key Values are only supported in the Data Explorer Metric Builder.
To add a Calculated Key Value, select "+Add a key value" on the Chart Tab of the Metric Builder. Instead of selecting a column from your table of data, select "Calculate a Key Value" at the bottom of the dropdown. This will open the "Calculate a Key Value" modal where you enter the custom calculation for your key value.
Enter Your Calculation
In the Calculated Key Values modal, start typing in the box to enter your expression. Grow provides a helpful autofill to help guide you in entering a valid expression. You can start typing aggregations or column names and the autofill will suggest the correct syntax.
Please note that any column you want to reference from your transactional table of data must be wrapped by an aggregation (with the exception of criteria columns in conditional aggregations).
Supported Syntax and Examples
Calculated Key Values use Google Sheets like syntax for all Basic Aggregations and Conditional Aggregations. However, please note that the syntax for column names and syntax highlighting for different arguments is the same as Calculated Columns on the Data Tab. Calculated Key Values also supports the same operators as the Filter Transform.
The following operators and aggregations are currently supported in Calculated Key Values:
Basic Aggregations
| Syntax | Description |
SUM([column_name]) |
sum of range |
COUNT([column_name]) |
count of range (includes blank cells) |
AVERAGE([column_name]) |
average of range |
MAX([column_name]) |
maximum of range |
MIN([column_name]) |
minimum of range |
COUNTUNIQUE[column_name]) |
unique count of range (does not count blanks) |
Mathematical Operators
When using multiple mathematical operators in the same expression, please note the calculation will respect order of operations.
| Syntax | Description |
+ |
addition |
- |
subtraction |
* |
multiplication |
/ |
division |
Expression Examples
MAX([Deal Amount])
This expression finds the largest sale value over the selected date range
SUM([Deal Amount])/COUNT([id])
This expression runs the sum of sales divided by the total number of orders over the selected date range, calculating average order value.
Conditional Aggregations
| Syntax | Description |
SUMIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the sum of a column depending on the criteria specified |
COUNTIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the count of a column depending on the criteria specified |
AVERAGEIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the average of a column depending on the criteria specified |
MAXIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the max value of a column depending on the criteria specified |
MINIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the min value of a column depending on the criteria specified |
COUNTUNIQUEIFS([column_name],[criteria_column1], "criteria1",[criteria_column2],"criteria2",...) |
returns the unique count of values in a column depending on the criteria specified |
Parameter Definitions
-
[column_name]the column to perform the aggregation on (ex: the column to be summed or counted) -
[criteria_column]the column (or range) to check the specified criteria against -
"criteria1"the specified criteria to run against the criteria column -
[criteria_column2],"criteria2",...additional columns (or ranges) and criteria to check
Filter Operators
| Syntax | Description |
= |
equals |
<> |
does not equal |
> |
greater than |
>= |
greater than or equal to |
< |
less than |
<= |
less than or equal to |
CONTAINS("criteria") |
checks for cells that contain defined string criteria |
DOESNOTCONTAINS("criteria") |
checks for cells that do not contain defined string criteria |
ISONEOF("criteria1","criteria2",...) |
checks for cells that equal defined criteria |
ISNOTONEOF("criteria1","criteria2",...) |
checks for cells that do not equal defined criteria |
ISBLANK |
checks for empty cells |
ISNOTBLANK |
checks for non-empty cells |
Syntax Notes
- Criteria specified in
CONTAINS(),DOESNOTCONTAIN(),ISONEOF(),ISNOTONEOF()is not case sensitive -
You can apply the
CONTAINS()orDOESNOTCONTAIN()operator on any data type (text, numeric, or date column).
Expression Examples
SUMIFS([sales], [sales_rep], "Austin")
This expression sums Austin's sales over the selected date range.
SUMIFS([order_id], [lead_source], ISONEOF("Social", "Paid"))
This expression sums the number of orders where the lead source equals "Social" or "Paid."
COUNTIFS([order_id], [sale_amount], ">100", [type], CONTAINS("New"))
This expression counts the number of orders where the sale amount is greater than 100 and the customer type contains the string "New"
COUNTIFS([order_id], [sales_amount], ">100", [customer_type], ISNOTBLANK)
This expression counts the number of orders where the sale amount is greater than 100 and customer type is not blank
COUNTIFS([Deal Amount], ">100")
This expressions counts the number of deals where the deal amount is greater than 100
-
You will notice this expression does not explicitly define the criteria_column. If you are entering a single criteria conditional aggregation, and the column you are aggregating is the same column you wish to run your criteria against, you do not need to specify the criteria_column.
Edit a Calculated Key Value Expression
To edit a Calculated Key Value expression, simply click the pencil of the Key Value you would like to edit. Upon clicking the pencil, you will be presented with the Calculated Key Values modal where you can edit your expression. You must click "Save" or "Cancel" to exit the modal. Clicking "Cancel" will not save any changes made to the expression.
Name Your Calculated Key Value
When you save out of the Calculated Key Value modal, Grow automatically gives the Key Value a Title. You will notice it is called "Calculated Key Value" plus whatever number key value it is (ex: "Calculated Key Value 2"). If you would like to customize the Title of your Key Value (or remove it), you can do so in the popover under the 3-dot menu.
Changing the name of the Title will be reflected in the text under the Key Value on the Chart and Metric Tile. There are no naming restrictions on Title names although we do advise you to use distinct Titles so you can match expressions to the Title.
Calculated Key Values and Data Explorer Compatibility
Calculated Key Values are supported on all Data Explorer Chart Types.
Chart Transforms and Calculated Key Values are independent of each other, you can add a Calculated Key Value and leverage any Chart Transforms in the Metric Builder.