Microsoft Dynamics 365 Customer Experience Analyst : Configure calculated and rollup columns
Calculated and rollup columns in Dataverse are powerful tools that allow you to automate data processing inside your tables without writing code or creating complex flows. A calculated column dynamically derives its value using formulas that reference other columns, related tables, or built-in functions—for example, automatically computing a customer’s full name by concatenating first and last names, or calculating an invoice’s remaining balance. A rollup column, on the other hand, aggregates data from related rows, such as summing total revenue from all opportunities linked to an account or counting the number of active cases for a contact. Rollup columns refresh either manually or on a schedule, ensuring performance is optimized for large datasets. Together, these features simplify business logic, improve data accuracy, and keep your Dataverse records up to date without external automation or custom development.
From a technical and logical perspective, calculated and rollup columns in Microsoft Dataverse are server-side data processing mechanisms that enhance performance and reduce the need for custom automation or plug-ins.
Calculated columns are fields whose values are derived at runtime through an expression defined in the Dataverse formula editor. They execute on the server every time the record is retrieved or updated, ensuring the value is always current. These formulas can reference other fields in the same table, parent or related tables, and support conditional logic, arithmetic, text manipulation, and date/time functions. The computation is synchronous, so calculated values are instantly available when a record is saved or queried.
Rollup columns aggregate data across related records. They perform operations like SUM, COUNT, MIN, MAX, or AVG over child records (e.g., total revenue from opportunities linked to an account). Rollup logic runs asynchronously via system jobs, which are triggered on demand, on a schedule, or when related data changes. To improve scalability, rollup fields store their computed value in the database, avoiding recalculation on every query.
Logically, calculated columns are ideal for real-time, per-record formulas, while rollup columns suit scenarios where aggregate values across relationships are needed, but near-real-time updates are not essential. Together, they enable declarative business logic, offloading processing from client apps and flows, and maintaining a clean, performant data model.
Calculated Columns
A calculated column lets you define an expression that runs on the server every time a record is saved or retrieved.
How to configure
1. Open your table
- In Power Apps, go to Dataverse → Tables.
- Choose the table where you want the column.
2. Add a new column
- Click + Add column.
- Enter a Display name, Name, and choose an appropriate Data type (e.g., Text, Number, Date).
3. Set the column type
- Under Advanced options, change Column type to Calculated.
4. Define the calculation
- Select Open formula editor.
- Write your expression (e.g.,
If([Revenue] > 50000, "High", "Low")
Use other fields, related table columns, or built-in functions.
5. Save and publish
- Click Save & Close, then Publish all customizations.
Technical notes
- Values are computed synchronously (instantly visible after save).
- Calculations cannot use aggregates across records — they work per record only.
- Supported in most client apps, SDK calls, and reports.
Rollup Columns
A rollup column calculates aggregates over related records and stores the result in the database.
How to configure
1. Open your table
- In Power Apps, navigate to the desired table.
2. Add a new column
- Click + Add column.
- Choose a Data type compatible with rollups (Whole Number, Decimal, Currency, or DateTime).
3. Set the column type
- Change Column type to Rollup.
4. Define the rollup logic
- Open the Rollup editor.
- Choose the related table (e.g., Opportunities for an Account).
- Select an aggregate function (SUM, COUNT, MIN, MAX, AVG).
- Add filters if needed (e.g., only include Active opportunities).
5. Save and publish.
6. Calculate values
Rollup columns update via:
- System jobs (scheduled every hour by default).
- “Recalculate” button on a record.
- Programmatic calls to `CalculateRollupFieldRequest` in the SDK.
Technical notes
- Stored in the database, making queries faster.
- Updates are asynchronous to optimize performance.
- Best suited for totals, counts, or averages across related records.
Best Practices
- Use calculated columns for formulas needed in real time.
- Use rollup columns for aggregates or totals that don’t need immediate refresh.
- Minimize complex formulas or deep relationship chains to avoid performance issues.
- Test security roles: users must have read rights to referenced fields/tables.
Summary:
Calculated columns let you define server-side formulas that evaluate instantly when a record is saved or retrieved. You configure them by creating a new column, setting its type to Calculated, and using the formula editor to define logic based on fields or related columns. They’re best for real-time, per-record computations.
Rollup columns aggregate data from related records (e.g., SUM, COUNT, AVG) and store the results in the database. To set one up, create a column of a supported data type, select Rollup, and configure the aggregation rules in the rollup editor. Rollup fields update asynchronously via background jobs, on-demand recalculation, or API calls.
Use calculated columns for immediate formulas and rollup columns for aggregated data. Publish your customizations after configuration, and consider performance and security when designing complex formulas or rollups.
Published on:
Learn more