Use Calculated Fields

For example, consider a calculated field Final Cost that computes an item's final cost for a customer with the formula:

[Price]*(1-[Discount])*(1+[Tax Rate])

• The fields are in brackets: Price, Discount, and Tax Rate.

• The operators are multiplication (*), subtraction (-), and addition (+).

• The numeral 1 is a literal.

Calculated fields can be dimensions or measures depending on their characteristics and use. Calculated dimensions are qualitative, categorical fields. Calculated measures are quantitative, numerical fields. In both cases, the formula is the crucial part of defining a calculated field. See Formulas in Calculated Fields.

Note

Calculated insights aren't quite the same thing. For more information, see Calculated Insights.

Calculated Dimensions

A calculated dimension is used to segment, classify, or group the data. Dimensions are typically discrete and often have a data type of text, boolean, date, datetime, URL, email, or phone number.

Use cases:

• Consolidate dimensions: If you have dimensions containing different pieces of information that are often used together you can concatenate them. For example, if there's a dimension for first name and a dimension for last name, you can combine them into a dimension for full name:

  • [First Name] + " " + [Last Name] to combine first and last name (with a space between them)

  • [Last Name] + ", " + [First Name] to combine them as last name comma first name (with a space after the comma)

• Group dimensions: If you have detailed values that you want to combine into categories, you can evaluate values and group them. For example, the following calculation takes five different types of book formats and groups them into two simple categories, paperback or hardcover:

  CASE [Book Format] 
  WHEN "Mass market paperback" THEN "Paperback" 
  WHEN "Trade paperback" THEN "Paperback" 
  WHEN "Paperback graphic" THEN "Paperback" 
  WHEN "Hardcover" THEN "Hardcover" 
  WHEN "Hardcover graphic" THEN "Hardcover" 
  END

Calculated Measures

A calculated measure, like all measures, are aggregated. They're typically continuous and often have a data type of number, percentage, or currency. Since measures are aggregated, quantitative fields, they have additional metadata such as aggregation type, decimal place, data role, and sentiment.

Use Case:

• Key performance indicators (KPIs): If you have various metrics native to your data source, you can use a calculated measure to compute a rate based on those fields:

  • SUM([Unique Email Opens]/SUM([Email Sends]) to define the Email Open Rate