Skip down to the research record

Rollup limits: 4 checks before a deal total column

Quota Forecast Editorial Team · Published · 11 min read

Check related rows, hierarchy depth, field caps and record relationships before trusting a rollup deal total in a forecast.

Key takeaways

  • name the relationship behind each deal-total column before you use its figure
  • list every record tied to a different parent, because the total may leave those records out
  • note the calculation limit of 50,000 related rows and the hierarchy depth limit of 10 beside each column
  • compare your MaxRollupFieldsPerOrg and MaxRollupFieldsPerEntity settings with the up to 200 and up to 50 values before adding columns
  • treat any error message about a limit as a stop sign for that column's total

A rollup can be done only over related tables with the 1:N relationship, according to Microsoft. The same guide names a calculation limit of 50,000 related rows, a hierarchy depth limit of 10, and settings that can go up to 200 for rollup fields across an org and up to 50 for each entity, according to Microsoft. Read those limits before you accept any deal-total column as a forecast figure.

A rollup depends on which records its relationship reaches, and that is where a total and a grid can part ways. The case C2 isn't included in the total, as it's directly related to the contact, not to the account, according to Microsoft. Check the relationship first, then the limits, then the record list before you use the number.

Why a rollup total can differ from the grid

A deal-total column and a grid of related records can show different numbers, and a forecast review is where that gap tends to get noticed. The first place to look is the relationship. Name that relationship in writing for every deal-total and case-count column you own, before you trust its total in any meeting.

The row and depth limits affect calculation; the field caps affect how many rollup fields you can create, as described by Microsoft. Each of those limits is covered in the sections below, so a reviewer can check each column against them. Ask whether any column has reached one of them before the number is read aloud in the meeting.

Keep the grid view and the total view side by side while you prepare, and write down which records each one includes. For a fuller walk through that comparison, see reconcile sales dashboard totals. A total that matches the grid is a useful signal, but agreement between the two views does not, by itself, confirm a forecast number. Treat the grid as evidence to reconcile, not as a finished answer.

Each deal-total column should carry its own short audit note. Record the table it reads, the relationship it uses, and the parent record it attaches to. A note like that lets the next reviewer repeat the check without asking the column's original author what was intended.

The table below separates a calculation error from pointers about formula fields and plan-based field limits. Read each source in full before you build a column from it.

PublisherPageQuoted sentence
MicrosoftDefine rollup columns with Power AppsIf the limit is exceeded, you see an error message: Calculations can't be performed online because the calculation limit of 50,000 related rows has been reached.
PipedriveWhat types of custom fields are there?You can learn more about formula fields in this article.
FreshsalesWhat are custom field limits?When you downgrade your plan, so does your maximum field limit.

The message in the table above names the related-row calculation limit in Microsoft's guide. Treat that message as a stop sign for the forecast figure in that column.

Your check starts with the count of related rows that each rollup column reads. Count those rows for every deal-total and case-count column before the review, and compare each count with the 50,000 figure from Microsoft's guide. A column that sits near that figure should be reviewed first, because it has the least room under the ceiling.

Record the count and the date it was taken beside the column name, so a reviewer can see which total used which number of related rows. Keep that note with the column definition rather than in a separate file. That way the limit stays visible when someone new opens the sheet.

If a column reaches the ceiling, do not forecast from its last total. Your team can ask for the column to be rebuilt against a narrower relationship, and the figure should stay out of the forecast until the new count sits below the limit in your notes.

This 50,000 figure comes from Microsoft's guide for its rollup columns, so it describes that product and not a limit you can assume in another CRM. Note the figure beside the error message text, and label it as Microsoft's figure so no one carries it into a different system.

Limit two: the hierarchy depth of 10

Microsoft's guide describes the hierarchy error this way: "If the limit is exceeded, you see an error message: Calculations can't be performed online because the hierarchy depth limit of 10 for the source row has been reached." The phrase for the source row names the record the rollup starts from. Depth is therefore measured from that record.

Your check should begin at that source row, then follow each parent link in turn. Map the chain for every deal-total column in writing, because the guide ties the depth error to the source row. A short map is enough, as long as each link is named.

Count the links in each chain and note the number beside the column, so a reviewer can see how close it sits to the depth limit of 10. A chain that is short for one column may be long for another, so measure each column separately instead of assuming they match.

Keep depth and row count on separate lines of your review notes, because the guide gives each limit its own error message. A column can pass a row check and still need a depth check, so one review does not cover both.

When a chain sits at the limit, bring the parent structure to the next review and ask whether the column still needs its deepest link. Your reviewer can then decide whether the total is fit for the forecast or only useful as background.

Limits for rollup fields in an org and in each entity

MaxRollupFieldsPerOrg, up to 200

Microsoft's guide says to "Change the values for MaxRollupFieldsPerOrg (up to 200) and MaxRollupFieldsPerEntity (up to 50) that you want." Your first step is to find out which values your organization currently uses. Write them down with the date you checked, so the next person knows what the column was built against.

Record that ceiling in your deal-total notes before anyone plans a new rollup field. The count of existing fields then sits next to the figure the guide gives.

MaxRollupFieldsPerEntity, up to 50

Note the entity figure beside the table where your deal-total and case-count columns live, because the guide names this setting for each entity, according to Microsoft. A reviewer can then see the setting next to the columns it governs.

Freshsales handles a related question differently, according to Freshsales: its maximum field limit follows the plan when you downgrade. That rule covers Freshsales plans, so it does not set the Microsoft figures above.

Pipedrive points readers elsewhere for formula fields, according to Pipedrive. Follow that pointer before you assume any cap applies to a formula field in your deal-total column.

Which records a rollup leaves out

Microsoft's example shows the Cases grid on the Account form for the A1 row with two cases, C1 and C2, according to Microsoft. The grid is the list a person sees, and it is the first thing to compare against the total. Note both cases before you read the number.

After aggregation, the Total Number of Cases is equal to 1, counting case C1 alone, according to Microsoft. Your review should compare that total with the grid row by row. A gap between the two is the signal to check the relationship.

The guide's own explanation for the gap is that case C2 can't be explicitly defined in the account rollup column definition, according to Microsoft. Your column should be built on the relationship that matches the parent you want.

A rollup can't be done over the 1:N relationship for the Activity table or the Activity Party table, according to Microsoft. Your deal-total column should not use either table as the source of a deal total. Activity counts belong in a separate check, such as activity linking rules.

Walk the example against your own data by listing each case or deal, its parent record, and whether the total includes it. Mark each record tied to a different parent, then compare that list with the grid before the review starts.

Illustrative example: counting rows against the ceiling

A sales team compares a grid of 200 deal rows with a rollup that includes 200 rows. Both counts are illustrative. The matching counts look reassuring, but the team also checks each row's parent. Suppose 40 grid rows belong to a different account. Those 40 rows need a closer look: matching counts alone do not show that both views include the same records. The team lists the parent links beside the column name, then checks record inclusion before using the total in the forecast call.

Filtering open deals by revenue

The guide gives a filter example for a deal-total column: "For example, you include the revenue from the open opportunities with an annual revenue greater than $1,000," according to Microsoft. Your notes should state that filter in plain words before the column goes live. A reviewer can then check the total against the same words.

Keep the filter and the rollup limits on separate lines of your review notes, so each can be checked on its own. A filter that passes review does not replace the row count or the depth check described above.

Compare the filtered total with the unfiltered grid, and write down each open deal that falls below the threshold. For common causes of such gaps, see forecast total mismatches. That list shows the reviewer which deals the filter removed and why they were removed.

Revisit the filter whenever someone edits the deal-total column, and record who changed the threshold and when.

Your next step

Today, open your top deal-total column, check which relationship it rolls up over, and list any records tied to a different parent.

FAQ

An error message appears saying that online calculations can't be performed, according to Microsoft. Stop relying on the total until the column is rebuilt against a smaller set of rows.

How many rollup fields can one table have?

Up to 50 for MaxRollupFieldsPerEntity and up to 200 for MaxRollupFieldsPerOrg, according to Microsoft. Check the entity figure for the table where your columns live, and record both values from your own settings before you plan new columns.

Why is a rollup total lower than the cases grid?

The guide's example is case C2, which the account rollup leaves out, according to Microsoft. Compare each case's parent with the column definition before you trust the total.

Can a rollup run over the Activity table?

No: the guide rules out rollups over the Activity table and the Activity Party table, according to Microsoft. Keep activity figures in a separate check, outside any deal total.

Does a Pipedrive page cover formula fields?

The custom-field page links to another article about formula fields. Note the link in your notes beside any column that uses formula fields.

Does downgrading a Freshsales plan change field limits?

Yes: according to Freshsales, the maximum field limit changes when you downgrade your plan. Check your current plan and its maximum field limit before you add new custom fields.

Sources