Break Back Data in Account, Level, and Dimension Rollups

Know the rollup calculation to understand how the breakback method works.

To change values on a version scenario, create a scenario version in

Model Management

Ensure that you have access to any versions or scenario versions where you want to change data.

Security: Editable Sheet Access
permission.

Rollups sum or average the values of entries according to preset structures. In sheets, rollups are blue. Entering data in rollups automatically populates the visible contributing cells according to breakback patterns.

As a best practice, you can enter data in rollups when the desired total is more important than the details or contributing factors. Example: The Sales parent level has over 50 descendant levels. You know that the forecasted product revenue for July is 800,000. You can enter 800,000 at the Sales parent level. Then, let the breakback method calculate the values for all the descendant levels.

Open Sheets.

(Optional) To propose changes to the base data using a scenario version, from the version prompt, select the base version to expand it.

(Optional) Select the scenario version that’s nested inside the base version.

(Optional) From the toolbar, click

Display Options

and uncheck the

Suppress rows if all zeros or blanks

check box.

Revealing all rows can help ensure that all contributing cells are visible. All the contributing cells must be visible for all the breakback options to be available.

Change the sheet view:

For cube sheets, click

Change Dimensions

and add the custom dimension, level, or account to the rows as the last nested dimension. Rollup cells for level and custom dimensions only display if they're the last nested dimension in the rows. Account rollups display as rows or as columns.

For standard sheets, right-click in the first column and select View by Accounts
to add values to account rollups.

Enter a value in a blue rollup cell and press

Enter

When you press

Enter

, you get prompted to select a break back method.

You might receive no breakback prompt because the rollup calculation is Last
. For Last
rollups, the rollup value distributes to the last cell only and leaves other contributing cells as-is.

As you select a breakback method, consider:

選項	說明
Proportionally	Distributes in proportion to existing values in visible contributing cells. Proportionally is the only method available if you can't view all cells on the sheet.
Evenly	Divides the rollup evenly or copies the average into all contributing cells. Not available if you can't see all contributing cells in the sheet.
Proportionally using prior year's values	Uses the prior year's value for each contributing cell and distributes proportionally. Not available if you can't see all contributing cells in the sheet.
Choose an assumption	Drop-down menu with breakback methods custom-created for your instance. Not available if you can't see all contributing cells in the sheet.

The contributing cells populate with values.

Enter values:

Cell 1: 100
.

Cell 2: 200
.

Cell 3: 300
.

After you save the sheet, enter 1000
in the rollup. Depending on the time rollup calculation and the breakback method, the contributing cell values change:

Method	For Sum Rollups	For Average Rollups
Proportionally	Cell 1: 167 Cell 2: 333 Cell 3: 500	Cell 1: 500 Cell 2: 1000 Cell 3: 1500
Evenly	Cell 1: 333 Cell 2: 333 Cell 3: 333	Cell 1: 1000 Cell 2: 1000 Cell 3: 1000
Proportionally using prior year's value	Similar to Proportionally except uses the prior year rather than the current values in the cells.	Similar to Proportionally except uses the prior year rather than the current values in the cells.