Choose layout and columns
Configure columns and layouts you want sellers to see in the forecast grid. Add from option set, change column type, show columns in chart, and more.
License and role requirements
| Requirement type | You must have |
|---|---|
| License | Dynamics 365 Sales Premium or Dynamics 365 Sales Enterprise More information: Dynamics 365 Sales pricing |
| Security roles | System Administrator or Forecast Manager More information: Predefined security roles for Sales |
Add and configure columns
In the Layout step of the forecast configuration, you can configure columns to define the forecast grid that appears for users. You can configure rollup columns from option sets that are defined for opportunities in your organization.
Note
To add a column that has field-level security, provide read access to the user "# Dynamics 365 Sales Forecasting" in the field security profile. For more information on configuring security profiles, see Field-level security to control access.
Perform the following steps to add columns and configure the forecast grid:
Add columns from an option set
Every forecast needs an option set to define rollup columns.
Important
The Prediction column is available only when predictive forecasting is enabled for your organization. To learn more, see About premium forecasting.
To define the layout, select an option set and then add the rollup columns accordingly, as described in the following steps.
In the Layout and column section, select Add from option set. A list of option sets appears.
Choose an option set entity from the rollup entity or from its related entities. The selected entity consists of option sets that are necessary to add columns to the forecast. In this example, the Opportunity option set entity is selected from Related Entities. The list shows only the option sets defined on the opportunity entity.
Choose an option set and then select Choose selected. In this example, Forecast category option set is selected. The option set values are added as columns for you to configure.
Tip
We recommend you use the out-of-the-box Forecast category option set for column configuration. After choosing this option, select Auto-configure in the dialog box that asks if you want to auto-populate the configuration parameters. This saves time by automatically populating columns according to best practices. The option to auto-configure columns are available only for Opportunity and Opportunity product rollup entities.
By default, a Quota column of type Simple is added to the forecast. To begin tracking against targets, you must manually upload an Excel workbook that contains quota values to the forecast. To learn more, see Activate and upload simple columns data.
After the rollup columns are added to the forecast, you can use More options to rearrange or remove the columns that you don't need.
Configure columns
In the Layout step of the forecast configuration, you can configure each column individually—such as changing the column type, allowing adjustments, and showing a column in the trend chart—according to your organizational requirements.
Note
- Auto configuration of columns are available only for Opportunity and Opportunity product rollup entities. For other rollup entities, you must manually configure each column.
- An alert icon
corresponding to a column, indicates that the column is not configured.
To configure each column, select the gear icon
and the column configuration section opens. In this example, the Pipeline column is selected.
On the column configuration page, the Name field specifies the display name of the column. The Unique name field is auto-populated based on the display name you've specified and supports only English characters. If you use non-English characters in the Name field, the Unique name field isn't auto-populated. You must manually enter a unique name in English for such columns.
Select the Type of column. The options that appear depend on the type of column you select. For example, when you select a column of type Calculated, a text box appears for you to enter a formula to use for calculating the value of the column.
The following column types are supported:
When configuring columns, you'll see a preview of the configuration in the Preview section.
Save and close the configuration.
Repeat steps 1 through 4 for each column that you want to edit.
Rollup type
The Rollup column type aggregates the values of all fields based on the selected Selector and Amount field values, by using the Date field value as the filter criteria.
While configuring these columns, some attributes are necessary for calculating the forecast estimated revenue and estimated close date. Typically, these attributes are available in the selected rollup entity. For complex data models, such as opportunity splits and opportunity product, these attributes are distributed between rollup and related entities. To address this complexity, the forecasting supports the selection of related attributes for estimated revenue and estimated close date.
Select the column Type as Rollup. The following options are available to configure.
| Parameter | Description |
|---|---|
| Selector | Select the option set value that defines how this column is grouped. For example, if Best Case is selected, only opportunities with the forecast category set to Best Case will be aggregated. |
| Data type | Select the data type of a column of which the aggregated value is displayed in the forecast grid. The supported values are currency, integer, and decimal. You can use integer or decimal data type for quantity-based forecast. By default, the value is selected as Currency. If the forecast contains both currency and numeric columns, then you must select the primary data type to activate the forecast. More information: Forecast with different data type. |
| Amount field | Select the amount field on which the aggregation is calculated. The list is displayed based on the selected Data type and Rollup entity. For example, to calculate the aggregation based on the estimated revenue for lost opportunities, select Est. Revenue in the Amount field. If you're using a custom field to track the estimated revenue for your organization, select that custom field. |
| Date entity | Select the entity from which you want to choose the date field. If the required date field is not available in the rollup entity, you can choose from the related entities. For example, in a product-based forecast, the rollup entity is Opportunity Product and the required estimated close date attribute will not be available in this entity. Therefore, select the Opportunity entity from Related entities that has the estimated close date attribute. |
| Date field | Select the date field that defines the record's forecast period. The available fields are based on the selected Date entity, such as Opportunity. For example, if the Opportunity entity is selected, the date field is configured as Est.Close Date. If you're using a custom field to track the estimated close date for your organization, select that custom field. |
| Description | Enter a description for the column. This description appears as a tooltip on the column header of the forecast grid, to help your users understand what the column contains. |
| Allow adjustments | Enable this setting to let users manually edit a system-calculated value directly in the forecast grid. When this setting is enabled, you'll see a pencil icon next to the forecasted value in that column. To learn more, see Adjust values in a forecast. |
| Show progress compared to quota | Enable this option to compare the column's value against the quota column. When this is enabled, a progress bar indicating the attainment percentage is shown below the column's value. |
| Show in Trend Chart | Enable this option to show the value in the Trend Chart. This option is available only when predictive forecasting is enabled for your organization. To learn more about trend charts, see Understand forecast projection through trend charts. |
Calculated type
Calculated columns can be identified in your forecast by looking for the information icon in the column header. Hovering over the icon will display the formula that is defined for the column. When you select column Type as Calculated, the values for the column are calculated and displayed based on a formula that you define. The following options are available to configure.
| Parameter | Description |
|---|---|
| Data type | Select the data type of a column of which the aggregated value is displayed in the forecast grid. The supported values are currency, integer, and decimal. You can use integer or decimal data type for quantity-based forecast. By default, the value is selected as Currency. If the forecast contains both currency and numeric columns, then you must select the primary data type to activate the forecast. More information: Forecast with different data type. |
| Calculation | Enter a formula to calculate values for the column. When you start typing a formula, suggestions are displayed for your convenience. The names shown are the column's unique name and value. For example, if you want to see the best case forecast in this column, enter the formula as Closed + Commit + Best Case. If you enter an invalid formula, an appropriate error message appears below the Calculation field. |
| Description | Enter a description for the column. This description appears as a tooltip on the column header of the forecast grid, to help your users understand what the column contains. |
| Allow adjustments | Enable this setting to let users manually edit the value of a calculated column directly in the forecast grid. When you select the column as adjustable, the formula you have created for this column must satisfy certain conditions that allow a proper rollup of values in the forecast. If not satisfied, an error is displayed, and you can't proceed to the next step. To learn more about the conditions and its corresponding errors, see Adjustment column conditions. When this setting is enabled, you'll see a pencil icon next to the forecasted value in that column. To learn more, see Adjust values in a forecast. |
| Show progress compared to quota | Enable this option to compare the column's value against the quota column. When enabled, a progress bar indicating the attainment percentage is shown below the forecast value in that column. |
| Show in Trend Chart | Enable this option to show the value in the Trend Chart. This option is available only when predictive forecasting is enabled for your organization. To learn more about trend charts, see Understand forecast projection through trend chart. |
Adjustment column conditions
To configure a calculated column as adjustable, it must satisfy certain conditions. These conditions apply to the columns that are used to create the formula. If the conditions aren't satisfied, an error will be displayed and you won't be able to proceed further.
| Condition | Description | Error type | Resolution |
|---|---|---|---|
| No column with adjustments | In the Calculation box, the formula must not contain columns that have been defined as adjustable. Example: You want to configure the committed column as a calculated field with adjustments. You entered the formula as won + forecast. Here, the forecast column has been configured as adjustable. When you select Allow adjustments, an error is displayed. |
Error: "The formula is invalid. The column reference - column results in a circular dependency." |
Remove the column that has been configured as adjustable from the formula and replace it with a non-adjustable column. |
| No simple or prediction columns | In the Calculation box, the formula must not contain any columns of the simple or prediction type. Example: You want to configure the forecast column as a calculated field with adjustments. You entered the formula as won + quota. Here, quota is a simple column. When you select Allow adjustments and then save the configuration, an error is displayed. |
An error of type Error while saving column is displayed with the message "You can't mark this column as adjustable because the column type of 'Column(display name) ' in the formula is not supported." Select OK to continue. |
Remove the simple or prediction columns from the formula. |
| No hierarchy-related columns | In the Calculation box, the formula must not contain any columns that are of type hierarchy related. Columns of this type won't be available through autocomplete in the Calculation box, but manually entering them will result in an error. Example: You want to configure the committed column as a calculated field with adjustments. You entered the formula as won + user. Though the user column is of type hierarchy related and isn't displayed in the suggested column list, you entered it manually. When you select Allow adjustments, an error is displayed. |
Error: "The formula contains invalid column reference - column at position number." |
Never use columns that are of type hierarchy related. |
| Formula must contain only addition and subtraction operations | In the Calculation box, the formula must contain only addition and subtraction operations. Other mathematical operations, such as multiplication or division, aren't supported. Example: You want to configure the forecast column as a calculated field with adjustments. You entered the formula as won * committed. Here, the operator is multiplication. When you select Allow adjustments and then save the configuration, an error is displayed. |
An error of type Error while saving column is displayed with the message "You can't mark this column as adjustable because the formula or the formula of one of its calculated columns contains non-supported operations." Select OK to continue. |
Remove the unsupported mathematical operations from the formula, and use only addition and subtraction operations. |
| No adjustable calculated field inside a formula of another calculated field | In the Calculation box, the formula can contain calculated fields. However, the calculated field you use must not be adjustable, and it must satisfy all the other conditions defined in this table. Example: You want to configure the forecast column as a calculated field with adjustments. You entered the formula as committed + pipeline. Here, committed is a calculated column that is set as adjustable. When you select Allow adjustments and then save the configuration, an error is displayed. |
An error of type Error while saving column is displayed with the message "You can't make this change because the formula contains 'Column(display name)', which is already an adjustable column." Select OK to continue. |
Remove the calculated column that is configured as adjustable from the formula. Add only non-adjustable columns. |
| No column can be set as adjustable after being added to the formula | After you create a calculated field that's adjustable, you can't set a column as adjustable if it has been used in the formula for that field. Example: You configured the committed calculated column by entering the formula won + best case and set the column to adjustable. Then you tried to change the won column setting to adjustable. When you select Allow adjustments and then save the configuration, an error is displayed. |
An error of type Error while saving column is displayed with the message "You can't mark this column as adjustable because it's being used in the adjustable calculated column 'Column(display name)'." Select OK to continue. |
Don't use any columns that you plan to make adjustable in the future. |
Simple type
Use the Simple column type to manually upload external data to a forecast. An Excel workbook is available for you to download after you activate the forecast. You must edit the Excel workbook with the necessary values and then upload it to the forecast. To learn more, see Activate and upload simple columns data.
For example, Quota is a simple column type where you can manually upload data by using an Excel workbook. The following options are available to configure.
| Parameter | Description |
|---|---|
| Data type | Select the data type of a column of which the aggregated value is displayed in the forecast grid. The supported values are currency, integer, and decimal. You can use integer or decimal data type for quantity-based forecast. Depending on the type of value that you'll be uploading data through an Excel workbook. By default, the value is selected as Currency. If the forecast contains both currency and numeric columns, then you must select the primary data type to activate the forecast. More information: Forecast with different data type. |
| Description | Enter a description for the column. This description appears as a tooltip on the column header of the forecast grid, to help your users understand what the column contains. |
Hierarchy related type
When you select column Type as Hierarchy related, the column helps you pull additional attributes from the hierarchy entity. The following options are available to configure.
| Parameter | Description |
|---|---|
| Data type | Data type of the column that you want to display on the forecast grid. By default, the value is selected as Single line of text and can't be changed. |
| Attribute field | Select an attribute to display in the column. For example, you can select Email, which will be unique to each user in your organization and can help you identify to whom the record belongs. |
| Description | Enter a description for the column. This description appears as a tooltip on the column header of the forecast grid, to help your users understand what the column contains. |
Can't find the options in your app?
There are three possibilities:
- You don't have the necessary license or role. Check the License and role requirements section at the top of this page.
- Your administrator hasn't turned on the feature.
- Your organization is using a custom app. Check with your administrator for exact steps. The steps described in this article are specific to the out-of-the-box Sales Hub and Sales Professional apps.
Related information
Configure forecasts in your organization
View a forecast
About premium forecasting
Configure forecast using custom rollup entity