---
schema_version: "1"
id: 19072247515510075da051669f6a2338b9643a6e6e8342b4c1fd40d0d97b166e
title: Calculate an Average Value in Salesforce Spiff Designer
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/sales/264-0-0/spiff_designer_average.md
canonical_url: https://help.salesforce.com/s/articleView?id=sales.spiff_designer_average.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: ef5a8fe1dc5df9ed154c153ad3bc001afcdd658ca3ceb77958611f2ae740a4a2
description: Some companies use averages in commission calculations. For example, companies often pay reps on the average of the total value of the contract, or Annual Contract Value, rather than just the first month's value or the total value. You can create a calculated value for the average in your datasheet based on the value and total duration.
products:
  - Sales Cloud
  - Sales Enablement
release: "264"
---
# Calculate an Average Value in Salesforce Spiff Designer

Some companies use averages in commission calculations. For example, companies often pay reps on the average of the total value of the contract, or Annual Contract Value, rather than just the first month's value or the total value. You can create a calculated value for the average in your datasheet based on the value and total duration.

### Required Editions

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small edition"><colgroup><col></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: both Salesforce Classic (not available in all orgs) and Lightning Experience</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: <strong>Enterprise</strong>, <strong>Unlimited</strong>, and <strong>Developer</strong> Editions</td></tr><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available for an additional cost in: <strong>Professional</strong> Edition with Web Services API Enabled</td></tr></tbody></table>

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small permission">
        <colgroup>
          <col style="width:50%">
          <col style="width:50%">
        </colgroup>
        <thead class="slds-text-title_caps ">
          <tr class="">
            <th scope="colgroup" class="slds-cell-wrap" colspan="2" style="text-align:center;vertical-align:top;" id="d52127e78">User Permissions Needed</th>
          </tr>
        </thead>
        <tbody>
          <tr class="">
            <td data-label="User Permissions Needed" class="slds-cell-wrap" style="vertical-align:top;" headers="d52127e78 ">To modify a datasheet:</td>

            <td class="slds-cell-wrap" style="vertical-align:top;" headers="d52127e78 ">
              <p>A Spiff <a href="https://help.salesforce.com/s/articleView?id=sales.spiff_users_roles_and_permissions.htm&amp;release=264.0.0&amp;type=5" title="Give users the appropriate level of access so they can be successful in Salesforce Spiff, and enforce the principle of least privilege. Assign a default role to each user, or create custom roles with access to specific Spiff functionality.">user role</a> with this permission turned on.</p>
              <p style="margin-bottom:0;">Designer Configuration: Manage</p>
            </td>

          </tr>
        </tbody>
      </table>

In this example, calculate the average of the total commission value of a contract.

1. Find or calculate the total commission value.
    1. If the value is a field in the CRM, make sure that the field is available for use. Hover over the object the field originates from on the Fields tab under Data in Designer. Click the three dots and select **Manage Fields**.
        
        A list of fields is available on that object. A checkmark means that the field is already available and can be referenced. If the field isn't checked, click the **+** button to make the field available for use.
    2. In Plans, go to the datasheet where you want to build the rule calculations and add a column for this field by clicking the **+** button on the far right column.
    3. Enter the name of the field to show it in the dropdown menu and select it from the list.
        
        ![Add a values column to the datasheet.](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-sales-sales_performance_management-264-0-0-production-enus/b1f2feb4-26b8-444f-97f7-be2349869d93/sales_performance_management/images/spiff/spiff_designer_average_add_value_field.png)
        
        The field populates on your datasheet.
        
        ![Values column in the datasheet](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-sales-sales_performance_management-264-0-0-production-enus/b1f2feb4-26b8-444f-97f7-be2349869d93/sales_performance_management/images/spiff/spiff_designer_average_add_value_datasheet.png)
2. Find or calculate the total duration.
    - If the CRM has a field for the total contract duration of the deal's contract, you can add that field in the same way you added the total value.
    - If you have a contract start date and a contract end date, you can use the `months_to()` function. Create a calculated field called TotalContractDuration and enter this formula, which returns the total duration of the contract in months.
        
        ```
        months_to(Contract_Start_Date__c,Contract_End_Date__c)
        ```
3. Divide the total value by the total duration. Create a new calculated field called AverageContractACV and enter this formula.
    
    ```
    If(TotalContractDuration=0,0,TCV__c/TotalContractDuration)
    ```
    
    The `if()` function makes sure that zero is returned if the TotalContractDuration is zero.
    
    ![Average Contract ACV calculation](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-sales-sales_performance_management-264-0-0-production-enus/b1f2feb4-26b8-444f-97f7-be2349869d93/sales_performance_management/images/spiff/spiff_designer_average_contract_acv.png)

#### See Also

- [if](https://help.salesforce.com/s/articleView?id=sales.spiff_function_ref_if.htm&release=264.0.0&type=5 "Tests for a condition in Salesforce Spiff and returns different values depending on the outcome.")
- [months\_to](https://help.salesforce.com/s/articleView?id=sales.spiff_function_ref_months_to.htm&release=264.0.0&type=5 "Returns the number of months between two dates or times in Salesforce Spiff. If the return value is a negative number, the second date occurred before the first date.")