Create Calculated Fields and Metrics

Learning Objectives

After completing this unit, you’ll be able to:

• Describe what calculated fields are.
  
• Create a calculated dimension.
  
• Create a calculated measure.
  

Explore Calculated Fields

You can add calculated fields to your semantic model when you need additional fields that don’t exist in your data. Think of calculated fields as on-the-fly fields that can contain complicated logic and exist on top of your data. There are two types of calculated fields:

• Calculated dimensions
  
• Calculated measures
  

Calculated dimensions are fields or dimensions that don't have numbers. Calculated measurements are custom calculations that you can also create on top of your existing data.

There are two ways to create calculated fields. You can either create them manually or you can use Semantics AI and have them drafted for you. You can choose what works best for you. But remember that by letting Einstein write the calculated fields for you, you don't need to know the exact fields in your data or how to use them. This reduces technical barriers for you and makes creating complex calculations very simple.

If Einstein Generative AI isn’t enabled in your org, you won’t see this option. For more information, see Salesforce Help: Set Up Einstein Generative AI.
You must turn on Semantics AI in the Feature Manager. For more information, see Salesforce Help: Enable Data Cloud Features.

Semantic Authoring is a pilot/beta service. Any use of Beta Services is subject to the Beta Services terms at https://www.salesforce.com/company/legal/agreements/ or a written Unified Pilot Agreement if executed by the Customer, and any applicable terms in the Product Term Directory.

To use Semantics AI, enter a description of the calculated field you’re creating and click Draft with Einstein. A draft is created for you with all the properties and the formula.

Create a Calculated Dimension

You can use a calculated dimension to classify, group, and apply other operations on dimension values. Calculated dimensions don’t physically exist in the database. Each calculated dimension is calculated as part of the query that can be referenced to from any report, pivot table, or dashboard page.

You can use a calculated dimension in these ways.

• Consolidate dimensions: If you have dimensions that display long names, such as email addresses, use the IF function to extract the name of the email without controlling the actual data in Data Cloud. For example: IF([Email_Domain] contains 'hotmail.autonto.com', ‘Hotmail’])
  
• Remove unwanted characters: If a dimension value includes characters that you want to extract, use the Extract()function. For example, EXTRACT([Email_Name],'—',0) extracts the email name.
  

Daniel wants to create a calculated dimension so he can flag high-priority cases when the case is open for more than 10 days. Here’s how to do it.

1. In the Semantic Model Builder, from the New dropdown menu, select Calculated Field.
   
   
2. In the New Calculated Field dialog, under Field Type, select Dimension.
   
3. From the Data Type dropdown menu, select the type of data you want to store in the calculated dimension.
   
4. In the Formula area, enter the formula using the correct syntax. This is the heart of the calculated field.
   
5. Enter a unique name.
   
6. To define advanced settings, expand Advanced and then enter a unique API name. You can’t edit the API name after creation.
   
7. To add filters to the semantic calculated dimension, click Filters.
   Here’s what the calculated dimension looks like when it’s done:
   
   
8. Click Save.
   

You can now use this new calculated dimension when viewing a dashboard of case health so you can compare the number of high-risk cases, and the number of low-risk cases.

Create a Calculated Measure

A calculated measure is a numerical custom calculation that you can create on top of your existing data. The calculated measurement doesn’t physically exist in the database, rather it represents a query that you can reference from any dashboard, report or visualization.

A calculated measure is calculated at run time according to a specific query context. It can be a simple mathematical expression that generates a calculation, or it can be a body of code with advanced conditioning and logic. A calculated measure has additional metadata like default aggregation, data type (number, currency, duration), and aggregation type.

You can use calculated measures to extract information from your data. For example, the Email Open Rate is calculated by dividing Unique Email Opens by Email Deliveries. With calculated measures, you can calculate the rate using Email Sends instead of Email Deliveries. Then, you can calculate the key performance indicators (KPIs) using a different formula.

Let’s see how Daniel can create a calculated measure to view the number of escalated cases over time.

1. In the Semantic Model Builder, from the New dropdown menu, select Calculated Field.
   
   
2. In the New Calculated Field dialog, under Field Type, select Measure.
   
3. From the Data Type dropdown menu, select the type of data you want to store in the calculated measure.
   
4. Enter a unique name.
   
5. To add a formula, enter the formula in the Formula box using the correct syntax.
   
6. Select Treat Nulls as Zeros if you want nulls to be treated as zeros in calculated measures.
   
7. To define advanced settings, expand Advanced.
   
8. Enter a unique API name. You can’t edit the API name after creation.
   
9. From the Aggregation Type dropdown menu, choose how you want to do numerical calculations on data. This lets the query return a summary of the expression's results.
   
10. Set the number of decimal places to display in the calculated measure.
    
11. From the Total dropdown menu, select how you want to display the total values.
    
12. From the Sentiment dropdown menu, select whether an upward trend is seen as good, bad, or neutral.
    
13. To add filters to the calculated measure, click Filters.
    Here’s what the calculated measure looks like when it’s done:
    
14. Click Save.
    You can now query this field and see the number of escalated cases and then act accordingly.
    

Create a Metric

A metric is a KPI tracked over time, such as revenue over the past year. The core components of a metric are a measure or calculated field representing the KPI, and a time dimension that tracks its development over time.

For example, you can create a sales performance metric to track overall sales across all regions, helping you assess whether your business is meeting its targets over a set period. Create metrics to have a single source of truth for all your business KPIs and to view the same metric in every dashboard, record page, or homepage on any platform.

You can also create metrics in Tableau Next. For more information, see Salesforce Help: Add a Metric to a Semantic Model.

Now, let’s see how Daniel can create a new metric that tracks the refund rate over time so he can help the sales analysts gain insights on the health of their sales performance.

1. On the Model tab, expand Metrics.
   
2. Click the plus sign next to Metrics.
   
   
3. Add a name and optional description to make the metric discoverable and easy to understand, and then click Next.
   
4. In the Value tab, select the measure you want to track and specify how you want to track it over time.
   
5. For Measure, search for and select the data point or calculation that you want to track. You can also add a new measure.
   
6. To define how to aggregate the data, click Measure Advanced Setup.
   
7. To add a filter, click Add Filter.
   
8. For Time Dimension, specify the time frame over which to track the measure.
   
9. To add context and granularity, click Additional Dimensions.
   You can add up to 20 dimensions.
   If you’re using Tableau Next to create metrics, these dimensions act as filters, displayed on the Metric Card Detail page. Insights that are toggled on will be calculated based on these dimensions.
   
10. Click Next.
    
11. (Optional) Adjust the insights by turning them on or off.
    

The Insights tab is only available for orgs that have Tableau Next.

Here’s what the metric looks like when it's done:

12. Click Save.
    

In this unit, you learned about calculated fields, what they are, and how they are used. You also learned how to create calculated dimensions and measures and how to create metrics in your semantic model. Head over to the next unit to learn how to test your semantic model, and how you can use the semantic model in other applications.