Spreading a total

A total in a cube is never typed in: a parent on a hierarchical dimension adds up its children, and a quarter or a year adds up its months. Planning often works the other way round. You know the annual budget for a region and want it distributed to every country and month underneath. Spreading does that.

What counts as a total

A cell is a total along a dimension when its item there is either:

• a parent in a hierarchical dimension (EMEA over its countries), or
• a formula item that is a plain sum: Q1 = Jan + Feb + Mar, a year of quarters, or SetSum(). The quarters, halves and years a time dimension generates are all plain sums.

A cell can be a total along several dimensions at once. The year on EMEA is a total by region and by time, and spreading it writes every country × month cell under it. It never writes into an intermediate total such as a quarter or a sub-region, because those are computed from what is written.

How to spread

Type the value on the total in the grid. Instead of refusing it, XCubes opens the Spread total dialog with your value filled in. You can also right-click a total and choose Spread total….

The dialog previews every cell it will write, with its value now and after. Spread writes them all, and one undo puts them all back. A cell that was empty before becomes empty again rather than holding 0.

If back-calculation is switched on for the cube, a value typed on a formula total is distributed straight away, without the dialog. A parent in a hierarchy always goes through the dialog.

Methods

• Proportional: keeps the current shape. Each cell takes the same share of the new total that it holds of the current one. This is the default when the cells hold anything.

• Evenly: equal shares. This is the default when the cells are empty.

• By profile: follows the shape of another slice of the same cube.

  • An item of another dimension: for example the Actual scenario, or a Seasonality line you keep for the purpose. The cells take the shares that item has across the same cells.
  • The same periods, prior year: for example, January's share of last year.

  The profile must hold something across those cells, or there is no shape to follow.

Round to rounds every value, for example to whole numbers or thousands. The rounding difference goes to the last cell, so the total still comes out at exactly the value you typed.

Locked cells and calculated cells

Some cells under the total cannot be written:

• cells locked individually;
• cells in a submitted or approved period;
• cells outside your access rule, or read-only under it;
• operands that are themselves calculated but not sums (an Adjustment line defined by a formula).

These cells keep their value. The rest share what remains: if you spread 200 and a locked cell holds 30, the others share 170, and the total still comes out at 200. The preview lists the cells that keep their value and why. When every cell under the total is in this situation, the spread is refused.

When a spread is refused

The dialog explains why. A spread is refused when:

• the cell is an input, not a total. Type the value directly;
• the total's formula is not a plain sum, such as a ratio or an IF. There is no set of cells it is the sum of;
• the measure aggregates by average, weighted, closing or none across a dimension being spread. A headcount averaged over the months of a year cannot be divided among the months, for example;
• the total covers more than 20,000 cells. Spread a smaller total.

Spread total and the Spread command

The Commands menu also has a Spread command. It divides a value evenly over the cells you have selected, whatever they are. Spread total starts from a total and works out the cells under it for you. Use it for budgets and targets.

For agents

The spread_value tool does the same thing. Call it with dryRun: true to get the plan first. The result lists every cell's value before and after, so a spread can be undone with set_cube_data.