Using the Assign feature, you can spread a value across multiple cells at a certain level of a dimension. Unlike spreading and distribution, there is not a direct link between a source cell and target cells.
To assign values, you specify a source value either by typing it or copying a cell value from the grid, and then you choose the target dimension and level and specify values for each target member. You can choose whether the values that you assign will overwrite the existing cell values, or be added to them.
Unlike spreading and distribution, assign operations can use the Cross Calculations dimension as the target dimension. This allows you to assign values to multiple restricted measures.
Before performing an assign operation, check that the table meets the requirements listed in Spreading, Distributing, and Assigning Values.
In the table, select one of the target cells for the operation.
You can select either a booked or unbooked value.
The Assign dialog appears with a Target Dimension and Level determined by the cell that you selected.
Set the Amount to assign.
This is the total value that you will assign to the target cells. You can type a value, or select (Use the values of the selected cell as total amount to assign) and choose a cell from the grid.
If necessary, change the target members by selecting a new dimension from the
Target Dimension list.
You can select any visible dimension other than Version and Account.
Some cell values cannot be changed, including cells that are calculated by a formula, local currencies, and locked cells. If you pick the Cross Calculations dimension, you cannot assign to two restricted measures that overlap each other (for example, Gross Sales in EMEA and Gross Sales in Germany).
- If necessary, change the dimension level using the (Roll up one level), (Drill down one level), and (Set to leaf level) buttons.
or hide additional options) to show more options:
Unbooked: Set this switch to On to show members that don’t have a value yet.
Assign by: Set this switch to Weights to use proportional weights instead of absolute values.
- Overwrite: Set this switch to On to overwrite the existing values of target cells instead of adding values to them.
Auto-fill pattern: Pick a pattern from the list to immediately set values for all target cells. The Even pattern distributes the amount evenly among all members. The remaining patterns are available when assigning monthly values; they assign the same pattern to the months of each quarter. For example, 4-4-5 assigns 4/13ths of the value to the first and second month of each quarter, and 5/13ths to the third month.
The monthly patterns are available when the Month level of the Date dimension is selected as the target, or when the Cross Calculations dimension is selected as the target and all of its members are restricted to a single month. If any restrictions are applied to dimensions other than Version and Date, these patterns will not be available.
For each target member, type the amount or weight that you want to assign from
the source value.
The dialog shows the percentage of the total amount to distribute for each member, a preview of the target member values after the distribution, and the remaining value to assign.
When assigning value by weights, you can select (Use values of the selected cells as weights for the spreading) and pick one or more cells from the grid. This allows you to quickly copy the proportions of existing values.
If you assigned values to unbooked cells, the values will be automatically booked to the leaf members of these cells. For operations that include a large number of unbooked leaf members, the Confirm Data Input dialog appears to limit the number of new values that are created.
Choose how to filter the dimension members that will receive values
automatically spread from the unbooked cells.
For more information on filtering the operation, see the Filtering dimension members for automatic spreading section in Spreading, Distributing, and Assigning Values.
- If you selected Refine Filters, select one of the dimensions not added to the table from the Dimension list, and choose the members that will receive values.
- Repeat the previous step for other dimensions as necessary, and then select OK.
The values of the target cells are increased or overwritten by the amounts that you specified. Cells that aggregate up to a booked target cell are adjusted proportionately.
- Choose how to filter the dimension members that will receive values automatically spread from the unbooked cells.