I'm trying to create a Cube Calculated account and am receiving a formula error that says "Cube Calculation Formulas must evaluate to zero when the cube's other accounts are zero." What am I doing wrong?
There are unique restrictions for the formulas that can be created in a Cube Calculation Account. The formulas for a Cube Calculation Accounts are required to always evaluate to zero at a particular location in the cube if all of the Cube's input accounts are empty at that location. If a user tries to enter a formula which would not satisfy that rule, the attempt to save the cube calculation will fail with an error: "Cube Calculation Formulas must evaluate to zero when the cube's other accounts are zero.”
The reason for this is that Cube sheets are not intended as a way to display or report on data from other areas in the model, i.e. modeled sheets, other cubes for display purposes only. The cube sheet is intended as a tool that can be used for data entry at the intersection of multiple dimensions. You can then use the Cube Calculation accounts to calculate data entry within the current cube.
To create a valid formula in a Cube Calculation account, you have to condition the account based on another account in the current cube sheet. For example, you could create a formula that looks something like:
Cube Metrics allow you to directly reference accounts outside of the cube that they reside in. Cube metric accounts can contain any formula, removing the need for "trigger" accounts.
The rollup of Metric accounts is similar to the rollup of Standard Metric accounts. Also, in a Cube Metric account, the account cannot reference itself in a formula, even with a time modifier. This is because of the way that the metric accounts are aggregated in the time rollups (quarter and fiscal years).