How to Create Rollup Columns (Fields) in Dynamics 365 CRM

Rollup Columns (previously known as Rollup Fields) can be extremely useful when you need an aggregated value using data from multiple Dynamics 365 CRM records. For instance, you could use a Rollup Column if your VP of Sales wants to see the total estimated revenue from open Opportunities for each Account directly on the Account Form.

In this article, I’ll explain how to create and use Rollup Columns, plus some limitations to be aware of.

In the older version of the Dynamics 365 interface these were called Rollup Fields. Microsoft has now renamed Fields to Columns (and Entities to Tables) and put all the settings into the Power Platform.

If you want to create a Column that calculates its value based on data from within a single record or its parent, check out Calculated Fields (now known as columns) instead.

Note: There are now 5 different Dynamics 365 apps that serve various CRM needs. In some contexts, you may also see those products referred to as “Customer Engagement” or “CE” apps. Rollup Columns work the same no matter which of those apps you’re using.

How to Create a Rollup Column in Dynamics 365 (The New Way)

You can create a Rollup Column by adding a new Column to your Table, choosing Rollup as the Column Type, choosing your Data Type, and building your logic in the same manner as a business rule or a Calculated Column.

Detailed steps and screenshots below. We’ll follow the example of showing the total estimated revenue from open Opportunities on each Account directly on the Account Form.

  1. In the Power Platform, create a new column for the relevant Table — in this case, Account.
  2. Choose your Data Type as you would when creating any new Column (single line of text, currency, etc.).
  3. Select Rollup as the Behavior.
  4. Click the Edit button below Behavior. (It appears once you’ve selected Rollup as the Behavior.)Edit column tab showing the edit button below the Behavior dropdown
  5. Build the logic of your Rollup Column. Six components make up the logic behind your Rollup Column:Logic options for a Rollup Column
    • Source Entity: This will default to the Table where you are creating your Rollup Column. In this case, that’s Account.
    • Use Hierarchy: Choose YES if you would like to include a parent/child relationship in your Table to roll up data across those relationships. In our example, we want to see total estimated revenue for all parent and child Accounts, so we’ll choose YES.
    • Source Entity Filters: You have the option of choosing additional filters/conditions for your source Table. If we wanted to see total estimated revenue for all related Accounts only in a certain city, or with a range of Account numbers, this is where we could select those filters.
    • Related Entity: Choose if you want to use data from a related Table in your Rollup Column. Since we want to get the total estimated revenue from each Account’s open Opportunities, we’ll choose Opportunities as our related Table.
    • Related Entity Filters: Just like source Table filters, you can choose additional filters for your related Table.
    • Aggregation: Here is where you choose the aggregate function for your data: SUM, COUNT, MIN, MAX, or AVG. In this case, we choose SUM.
  6. Save and close your conditions and then save and close your Column window.
  7. Open the Form you would like to add your new Rollup Column to. In our case, this is the main Account Form.
  8. Add your new Rollup Column to your Form.Screenshot showing the new Rollup Column added to the Account Form

Now when we open our Account Form, we see our new Rollup Column, Estimated Opportunity Revenue. Looking at the Account called A. Datum Corporation, we can see that our Rollup Column calculates the sum of all the Opportunities for the Account:Example account record with Rollup Column

The calculator icon identifies this as a Rollup Column. You can manually refresh your calculation online by clicking the Recalculate button.

Note: The “Last Updated” column will be automatically created and added to the form. There may be additional fields that get created based on the data type of the column.

Video Instructions for Creating Rollup Columns in D365

How to Create a Rollup Field in Dynamics 365 (The Old Way)

We no longer advise that you use the old Rollup “Field” approach described below. Instead, we advise you to use the new instructions for Rollup Columns.

Use the information below with caution.

You can create a Rollup Field by adding a new field to your Entity, choosing Rollup as the Field Type, choosing your Data Type, and building your logic in the same manner as a business rule or a Calculated Field.

Detailed steps and screenshots below. We’ll follow the example of showing the total actual revenue from each Account directly on the Account Form.

  1. Create a new field for the relevant Entity — in this case, Account. (If you are on the D365 new experience, you will create a new “column” for the relevant “table.”)
  2. Choose your Data Type as you would when creating any new field (single line of text, currency, etc.).
  3. Select Rollup as the Field Type.
  4. Click the Edit button. (It appears once you’ve selected Rollup as the Field Type.) Rollup-Fields
  5. Build the logic of your Rollup Field. Six components make up the logic behind your Rollup Field. I’ve labeled them a through e in the screenshot below.:
    1. Source Entity: This will default to the Entity where you are creating your Rollup Field.
    2. Use Hierarchy: Choose YES if you would like to include a parent/child relationship in your Entity to roll up data across those relationships. In our example, we want to see total Account revenue for all parent and child Accounts, so we choose YES.
    3. Source Entity Filters: You have the option of choosing additional filters/conditions for your source Entity. If we wanted to see total revenue for all related Accounts only in a certain city, or with a range of Account numbers, this is where we could select those filters.
    4. Related Entity: Choose if you want to use data from a related Entity in your Rollup Field. Since we want to get the total actual revenue, we’ll choose Invoices as our related Entity.
    5. Related Entity Filters: Just like source Entity filters, you can choose additional filters for your related Entity.
    6. Aggregation: Here is where you choose the aggregate function for your data: SUM, COUNT, MIN, MAX, or AVG. In this case, we choose SUM.
  6. Save and close your conditions and then save and close your Field window.
  7. Open the Form you would like to add your new Rollup Field to. In our case, this is the main Account Form.
  8. Add your new Rollup Field to your Form.Rollup-Fields

Now when we open our Account Form, we see our new Rollup Field, Total Account Revenue. Looking at the Account called Adventure Works, we can see that the field calculates the sum of all the Invoices for the Account:Rollup-Fields

The calculator icon identifies this as a Rollup Field. You can manually refresh your calculation online by clicking the Recalculate button. When you hover on the button, you can also see a popup that indicates when the last update happened.

Rollup Column Aggregate Functions

There are 5 different rollup functions, called “aggregate functions,” to choose from:

  • SUM
  • COUNT
  • MIN
  • MAX
  • AVG

All of those will be self-explanatory to most people who have used functions in Excel.

Rollup Column Use Cases

  • Similar to the example above, if your VP of Sales wanted to see total actual revenue of won Opportunities for an Account, we could create a Rollup Column for that.
  • A Rollup Column is also perfect if, for instance, the service department wants to see the number of high priority open Cases across all Accounts.
  • You can include Rollup Columns in forms, views, charts, and reports.
  • You need to have a Rollup Column as part of a goal metric.
  • And you can even use a Rollup Column as part of a Calculated Column.

Limitations of Rollup Columns

  • Rollup Columns are read-only.
  • There is a maximum of 100 Rollup Columns per instance and 10 Rollup Columns per Table.
  • Workflows cannot be triggered by Rollup Column updates.
  • Rollup Columns are only supported over related entities with a one-to-many relationship. A many-to-many relationship is not supported.

The values of the Rollup Columns are calculated by system jobs that run asynchronously in the background every hour. Mass updates run every 12 hours. Administrators can set when a job is run or pause the job. Also, users, when online, can manually refresh the field at any time.

Rollup fields are one of the many features in Dynamics 365 CRM solutions designed to improve efficiency and deliver quick insights. You may also want to read our instructions for calculated fields multi-select option sets.

If you’d like experts to improve or audit your D365 configuration for you, or if you want personalized advice, please contact us.

Dynamics 365 CRM How-To eGuide

41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 CRM apps. Includes interactions with Power Apps and Power Automate!

Get eGuide

Dynamics 365 CRM How-To eGuide

Get eGuide