Learn how to embed business logic directly into your Dataverse data model using formula columns and rollup columns. Build margin calculations, pipeline aggregates, and deal status signals — no code, no flows required.
Picture this: your sales team is using a model-driven app to manage opportunities. Every morning, someone opens Excel, pulls data from Dataverse, calculates each deal's margin percentage, figures out how many days each opportunity has been open, and emails the results to the team. It takes forty minutes, it's error-prone, and it's completely unnecessary — because Dataverse can calculate all of that automatically, right on the table, without a single line of code or a Power Automate flow.
Formula columns and rollup columns are two of the most underused features in Dataverse. Together, they let you embed business logic directly into your data model: deriving values from other fields, aggregating child records, and surfacing metrics that would otherwise require external tooling or complex automation. When you build this logic into the table itself, every app, view, and report that touches that data gets the calculated value automatically. It's one of the highest-leverage moves you can make as a data model designer.
By the end of this lesson, you'll know exactly when to reach for a formula column versus a rollup column, how to write the expressions that power them, and how to avoid the gotchas that trip up even experienced makers. We'll build a realistic sales opportunity scenario from scratch — margin calculations, days-open counters, and pipeline aggregates — so you leave with patterns you can adapt immediately.
What you'll learn:
You should be comfortable with Dataverse tables, columns, and relationships before working through this lesson. If you need a refresher on column types and how lookup relationships connect tables, read Dataverse Fundamentals: Tables, Columns, and Rows Explained for Power Apps Makers first. You should also understand how relationships and lookups are structured — that's covered in Designing a Dataverse Data Model: Relationships, Lookups, and Choice Columns.
Before touching the UI, you need to understand what each column type actually does under the hood, because that understanding drives every decision you'll make.
Formula columns compute a value from other fields on the same record (or from system values like today's date). The formula is evaluated by the Dataverse service layer at read time — meaning the value is computed fresh whenever the record is retrieved. Think of a formula column as a virtual field: it doesn't store a persisted value in the database row the same way a regular column does. It's closer to a calculated column in SQL Server — a logical expression that appears as a column in query results.
Rollup columns aggregate data from related child records. They do persist their value — Dataverse recalculates them on a scheduled basis (by default, every hour) and stores the result on the parent record. When you open an opportunity record and see "Total Revenue from Associated Orders: $47,250," that number lives in a rollup column. You can also trigger a manual recalculation.
The practical consequence of this distinction is significant:
| Characteristic | Formula Column | Rollup Column |
|---|---|---|
| Data source | Same record's fields | Related child records |
| Recalculation | On every read | Scheduled (default: hourly) |
| Can be used in views/filters | Yes, with caveats | Yes |
| Stores a value in the database | No (computed at query time) | Yes (persisted) |
| Supports aggregates (SUM, COUNT, etc.) | No | Yes |
| Available for sorting in views | Limited | Yes |
Key insight
Because formula columns are computed on read and rollup columns are refreshed on a schedule, there's a meaningful difference in "freshness." A formula column showing days since record creation is always accurate to the second. A rollup column showing total child record value might be up to 59 minutes stale — or more, if the system is under load.
Throughout this lesson, we'll work with a sales scenario built around three tables:
This structure is realistic: it mirrors what you'd find in Dynamics 365 Sales or any custom pipeline tracker. If you're following along in your own environment, create the Opportunity table with at minimum these columns: Estimated Value (Currency), Cost (Currency), Close Date (Date Only), Created On (default system column), and Stage (Choice: Prospect, Qualified, Proposal, Closed Won, Closed Lost).
For Opportunity Product, create: a Lookup to Opportunity, Unit Price (Currency), Quantity (Whole Number), and Discount Percentage (Decimal).
Navigate to make.powerapps.com, open your solution, find the Opportunity table, and go to Columns. Click New column. Give it the display name "Days Open," and in the Data type dropdown, select Formula. This reveals a formula editor directly in the column creation panel.
Formula columns use Power Fx — the same language you use in canvas apps. If you've written canvas app formulas before, you'll feel right at home. If not, the syntax is clean and readable: functions use English names like If, DateDiff, Concatenate, and Left.
For Days Open, enter this formula:
DateDiff(createdon, Now(), TimeUnit.Days)
createdon is the system column that records when the row was created. Now() returns the current datetime. DateDiff returns the integer difference between two datetime values in the unit you specify. The result is how many days the opportunity has been alive.
Save the column. Go to a view that includes this column, and you'll see a live integer for every opportunity — no flow, no plugin, no scheduled job.
Tip
Formula column names follow the same schema prefix rules as other columns. If your publisher prefix is cr5a2, your formula column's logical name will be cr5a2_daysopen. Reference this logical name if you need to access the column in a canvas app or API call.
Date calculations are where formula columns shine. Let's add a column that tells salespeople whether a deal is at risk of missing its close date:
If(
closedate < Today() && cr5a2_stage <> "Closed Won" && cr5a2_stage <> "Closed Lost",
"Overdue",
If(
DateDiff(Today(), closedate, TimeUnit.Days) <= 14,
"Closing Soon",
"On Track"
)
)
Warning
Choice column values in Power Fx formulas must match the label text exactly, including capitalization, as Dataverse resolves them as strings in this context. If your choice option is "Closed Won" but you write "closed won", the comparison will fail silently — the column will return the else branch for every record. Test your formulas against real records immediately after creation.
This formula returns one of three strings depending on the relationship between today's date and the close date, filtered by stage. Name this column "Deal Status Signal" with a data type of Text (the formula editor will infer this from the return type of your expression).
Now let's calculate gross margin percentage — a classic derived metric:
If(
estimatedvalue = 0,
0,
Round(
(estimatedvalue - cr5a2_cost) / estimatedvalue * 100,
2
)
)
This formula handles the divide-by-zero case explicitly (returning 0 when estimated value is zero), then computes (Revenue - Cost) / Revenue * 100 rounded to two decimal places. Name this "Margin Percentage" and set the data type to Decimal Number.
Note
Formula columns are read-only by definition — users cannot type into them in a form. When you add a formula column to a model-driven form, Dataverse automatically renders it as a read-only field. You don't need to configure this separately, but you should make it visually clear to users by placing formula columns in a dedicated "Calculated Metrics" section on the form. See Designing Model-Driven Forms: Sections, Tabs, Subgrids, and Quick View Forms for section and tab design patterns.
Formula columns aren't just for numbers and dates. They're useful for building display strings that combine data from multiple fields into something human-readable.
Suppose you want a single "Summary Label" column that combines the account name, stage, and estimated value for use in views and dashboards:
Concatenate(
cr5a2_accountname,
" | ",
Text(cr5a2_stage),
" | $",
Text(estimatedvalue, "[$-en-US]#,##0")
)
The Text() function with a format string converts the numeric currency value into a comma-formatted dollar amount. This kind of display column is genuinely useful in views where space is tight and you want one column to convey the key facts about a record.
Understanding the constraints saves you from spending an hour trying to make something work that simply isn't supported:
Filter() and LookUp() that work against data sources are not available in formula columns.Patch(), Notify(), or any function that modifies data.Rollup columns solve a genuinely different problem. Your Opportunity has multiple Opportunity Product line items. You want to show the total value of those products on the Opportunity record. You could write a Power Automate flow that recalculates a field whenever a product is added or updated — but that's fragile, slow, and requires maintenance. A rollup column handles this declaratively, directly in the data model.
On the Opportunity table, add a new column. Name it "Total Product Value," set the data type to Currency, and then look for the Column type option — you'll see choices for Simple, Calculated, Rollup, and (in newer interfaces) Formula. Select Rollup.
After saving the basic definition, click Edit next to the rollup configuration. The rollup editor has three sections:
1. Source Entity This is the parent table — Opportunity. You're configuring what gets calculated onto this record.
2. Related Entity Pick the child table — Opportunity Product. You must select the specific relationship (the lookup column on Opportunity Product that points back to Opportunity).
3. Aggregation Choose your aggregate function and the column to aggregate:
Click Save on the rollup configuration.
Tip
If you don't see the rollup option in the column type dropdown, make sure you've selected a data type that supports aggregation — Currency, Whole Number, Decimal Number, Date/Time, and a few others. Text columns cannot be rolled up.
Here's where rollups become genuinely powerful: you can filter which child records participate in the aggregate. Suppose you only want to sum the value of Opportunity Products where Quantity > 0 (to exclude placeholder lines entered with zero quantity):
In the rollup editor, find the Filter (Related Entity) section and add a condition:
Now the rollup only sums products that have been confirmed with a quantity. You can stack multiple filter conditions here using AND logic. This lets you build very specific aggregations — active line items only, products in a certain category, activities completed in the last 30 days, and so on.
Roll up columns support COUNT as an aggregate function, which doesn't require specifying a source column — it just counts the number of matching related records.
Let's add a rollup column to Opportunity called "Total Activities":
This gives every opportunity a running count of open tasks, calls, and emails. Display that on the form and in views, and your sales managers can instantly identify which deals have gone cold.
Key insight
COUNT rollups on the activity tables work against the underlying activitypointer entity, which is the polymorphic base table for all Dataverse activities. If you want to count only Phone Calls specifically, you'll need to filter on activitytypecode = phonecall. The filter condition is set in the rollup editor under the Related Entity filter section.
By default, rollup columns recalculate on a system-managed schedule — roughly every hour, though this can vary. For records where the aggregate is business-critical and you need a fresh number now, Dataverse provides a manual recalculation button on the form.
When a rollup column is present on a form, you'll see a small circular refresh icon next to the field value. Users can click it to trigger an immediate recalculation for that specific record. This is useful for demos, executive reviews, or any situation where "the number should be right, right now."
As an administrator or maker, you can also trigger bulk recalculation via the Dataverse system job interface, or by running a Power Automate flow that calls the CalculateRollupField action.
Both column types appear in the column picker when you're building or editing views — they behave like any other column for the purpose of display. But there are nuances worth knowing.
Formula columns are computed at query time, so including them in a view works cleanly. However, sorting by a formula column in a view is not always supported, and filtering on a formula column in a view's filter conditions may produce unexpected results or fall back to in-memory evaluation. If you need robust filtering and sorting on a calculated value, a rollup column (which stores a persisted value) is more reliable.
Rollup columns, because they store a real value, sort and filter exactly like standard columns. You can filter a view to show "Opportunities where Total Product Value > 50000" and it will execute as an efficient server-side query.
Learn more about building effective views in Creating and Customizing Views in Model-Driven Apps: Filters, Sorting, and Editable Grids.
Warning
Avoid putting formula columns in the filter conditions of views that will be used by large user populations against large datasets. Because the formula is evaluated per-record, filtering on it can result in full table scans. If you need a filterable derived value at scale, either use a rollup column or use a real-time workflow/plugin to persist the calculated value into a regular column.
Both column types are drag-and-drop additions to model-driven forms. A few best practices for presenting them well:
Group calculated fields together. Create a dedicated tab or section on the form labeled "Metrics" or "Calculated Values." This sets user expectations — they understand these fields are read-only summaries, not inputs.
Use column labels to communicate the calculation. "Margin %" is fine as a column name, but consider adding the form field description to explain what it means: "Gross margin as a percentage of estimated revenue." Form field descriptions appear as tooltips in model-driven apps and help users trust the number.
Don't lock users out of the underlying inputs. If your formula column depends on Estimated Value and Cost, make sure those fields are on the form too — ideally visible near the formula column so users can see the relationship between inputs and outputs.
Let's build a complete calculated data setup for the Opportunity table. Work through these steps in order:
Step 1: Days Open formula column
Create a formula column named "Days Open" on the Opportunity table:
DateDiff(createdon, Now(), TimeUnit.Days)
Data type: Whole Number (inferred). Save and verify by opening an existing opportunity record — the field should show an integer representing the age of the deal.
Step 2: Deal Status Signal formula column
Create a formula column named "Deal Status Signal" on the Opportunity table:
If(
closedate < Today() && cr5a2_stage <> "Closed Won" && cr5a2_stage <> "Closed Lost",
"Overdue",
If(
DateDiff(Today(), closedate, TimeUnit.Days) <= 14,
"Closing Soon",
"On Track"
)
)
Replace cr5a2_stage with your actual stage column's schema name. Data type: Text.
Tip
Find the logical name of any column by going to the column editor and looking at the "Name" field (not the Display Name). It will include your publisher prefix. You can also hover over columns in the solution explorer to see schema details.
Step 3: Total Product Value rollup column
On the Opportunity table, create a Currency rollup column named "Total Product Value." Configure the rollup:
Save. Open an Opportunity that has Opportunity Product records and click the manual refresh icon to trigger a recalculation. Verify the value matches what you'd calculate by hand.
Step 4: Total Activities rollup column
Create a Whole Number rollup column named "Open Activities":
Step 5: Build a view that uses all four columns
Create a new view on the Opportunity table called "Pipeline Health." Add columns in this order:
Sort by "Days Open" descending. Save and publish the view. Navigate to your model-driven app (if you have one configured — see Building Your First Model-Driven App: Site Map, Tables, Forms, and Views) and find this view. You now have a pipeline health dashboard built entirely from calculated columns, with no flows, no plugins, and no code.
Step 6: Add the formula columns to the Opportunity form
Open the Opportunity main form. Add a new tab labeled "Deal Metrics." Inside it, create a section with two columns and place: Days Open, Deal Status Signal, Margin Percentage, Total Product Value, and Open Activities. Save and publish the form. Open an Opportunity record and confirm all five calculated fields display correctly.
This usually means one of three things: the column(s) you're referencing don't have values on the records you're testing, your column schema names are wrong, or you're referencing a column type that the formula doesn't know how to handle.
Verify by simplifying your formula down to its component parts. Start with just estimatedvalue as the formula — does it return the right number? Then add the arithmetic. Then wrap it in the If(). Isolating the problem this way surfaces exactly where the formula breaks.
Not all Power Fx functions are available in Dataverse formula columns. Functions like Filter(), LookUp(), Collect(), and Notify() are canvas-app-specific and won't work here. The formula editor provides IntelliSense — if a function name doesn't autocomplete, it's not supported. Supported categories include: text functions (Left, Right, Mid, Trim, Concatenate, Text), math functions (Round, Abs, Sqrt, Mod), date functions (DateDiff, DateAdd, Today, Now, Year, Month, Day), and logical functions (If, Switch, And, Or, Not, IsBlank).
First check whether the related child records actually exist and meet your filter conditions. Open one of the child records directly and verify the values. Then manually trigger recalculation using the refresh icon on the parent record's form.
If manual recalculation works but scheduled recalculation doesn't, check the Dataverse system jobs in the admin center. Look for the Calculate Rollup Fields recurring job and verify it's running. Failed jobs often indicate a problem with the rollup definition that only surfaces at scale.
This is a known limitation. Formula columns can be displayed in views, but view filter conditions on formula columns may not work as expected. If you need to filter on a calculated value, options include:
Filter() function against the retrieved datasetNote
This is one of the most common pain points when transitioning from business rules (which also can't produce filterable column values) to formula columns. Business Rules in Dataverse: Validation and Field Logic Without Code covers what business rules can do, which helps you understand when to use each tool.
Currency columns in Dataverse store values in the organization's base currency internally. If you're doing math between currency columns that might be in different currencies (because your org has multi-currency enabled), you may get unexpected results. Use estimatedvalue_base and cr5a2_cost_base (the base-currency versions of currency columns) in your formulas if multi-currency is a factor in your environment.
Because formula columns are computed at query time, they add CPU overhead to every query that retrieves records including that column. For tables with millions of rows, a complex formula column included in a view retrieval can meaningfully increase query latency.
Mitigation strategies:
If() with many branches and complex string operations are more expensive than simple arithmetic.The one-hour default refresh cycle is appropriate for most business metrics. But if your business process requires near-real-time aggregates (for example, a live inventory count that drives order fulfillment decisions), a rollup column is the wrong tool. For near-real-time aggregation, consider:
Key insight
Dataverse rollup columns are designed for reporting and display — giving managers a quick view of the state of a parent record without querying all children in real time. They are not designed for transactional integrity. Don't use a rollup column to drive business logic that needs to be exact at the moment of a transaction.
Dataverse actually has a fourth calculated column type worth knowing: the legacy Calculated column (distinct from Formula columns). Calculated columns predate Power Fx in Dataverse and use a simpler expression editor. They're still functional and widely deployed, but Microsoft is positioning Formula columns (Power Fx-based) as the forward path. If you're building something new, use Formula columns. If you're maintaining an existing solution that uses classic Calculated columns, they'll continue to work, but consider migrating when you have the opportunity.
You've covered a lot of ground. Let's consolidate what you now know:
Formula columns use Power Fx to derive a value from other fields on the same record, computed fresh on every read. They're ideal for display metrics, date calculations, status signals, and string formatting — anything that combines values that already exist on the record into something more useful.
Rollup columns aggregate data from related child records using SUM, COUNT, MIN, MAX, or AVG. They store their result and refresh on a schedule. They're the right tool for totaling line items, counting activities, and surfacing parent-level summaries of child data.
The decision between them is usually straightforward: if the data lives on the same record, use a formula column. If it needs to aggregate child records, use a rollup column. If neither fits (cross-table reference, transactional accuracy, filterable derived value), use a flow or plug-in to write a persisted calculated value.
Both column types let you surface derived data in views and forms without building separate automation. They make your data model smarter, your apps simpler, and your users more capable — all without writing a single line of traditional code.
Where to go next:
As your model-driven app grows, you'll want to think about how calculated data interacts with access control. Rollup columns and formula columns inherit the table-level security of their parent record, but column-level security can restrict visibility of sensitive calculated metrics. Explore Column-Level Security and Record Sharing in Dataverse to understand how to protect calculated data appropriately.
You should also look at how calculated columns work alongside business process flows. When you're guiding users through a multi-stage pipeline, formula columns like "Days in Stage" and rollup columns like "Stage Revenue" provide the contextual data that helps users make decisions at each step. Business Process Flows: Guiding Users Through Multi-Stage Processes in Power Apps shows you how to put that into practice.