Formulas for Defining Measures

Formulas are used in measure and constraint definitions. They consist of row source variables, cube locations, and functions. Formulas are restricted by the dense and sparse dimensions of the cube to which they belong.

The computation of a measure consists of the following elements:

Dense calculation
Aggregation along dense dimensions
Sparse calculation along sparse dimensions or between cubes

Dense calculations are defined by formulas, and sparse calculations are defined by batch formulas and on-change formulas. Sparse calculations can be roll-up or allocation.

Every formula has a scope. For example, a dense formula and its scope define a family of formulas for the cubular cells in the scope. The dense formula is a multidimensional formula, and the formulas in the family are expanded formulas.

When writing formulas, keep in mind the following points:

Formula scope can be defined explicitly or inferred from other calculation elements.

In the following example, Beginning Inventory Units is a loaded measure. It has a dense formula and dense aggregation along the Manufacturing dimension. Beginning Inventory Units belongs to a cube that has two dimensions, Component (sparse) and Manufacturing (time/dense). As a result, the scope of the dense formula applies to the following members:

All members in the Component dimension (there is no sparse calculation)
Members at the weekly level in the Manufacturing dimension (there is a dense roll-up from week along that dimension)
Each cell of the Beginning Inventory Units measure where no loaded value exists (loaded values override calculated values)

<measure:Measure name="Beginning Inventory Units" 
     scale="0" type="loaded" styleName="unitStyle”>
     <measure:Formula>
          <![CDATA["Beginning Inventory Units"="Ending Inventory
          Units"[previous(Manufacturing)]]]>
     </measure:Formula>
     <measure:DataRowSource name="Inventory" columnName="quantity"/>
     <measure:SimpleRollUp summaryOperator="first_in_period">
          <measure:Dimension name="Manufacturing"/>
     </measure:SimpleRollUp>
</measure:Measure>

Note:

If a cell is modified by a user input value, the formula is still applicable to the cell. Integrated Operational Planning generates a calculation exception that denotes the discrepancy between the calculated value based on the formula and the user input value. A loaded value is considered a calculated value. If there is a difference between a loaded value and a calculated value from the formula, it is not considered as a discrepancy.

In the following example, Ending Inventory Units is a derived measure. It belongs to a cube has that has two dimensions, Component (sparse) and Manufacturing (time/dense). Its scope applies to the following members:

All members in the Component dimension (there is no sparse calculation)
Members at the weekly level in the Manufacturing dimension (there is a dense roll-up from week along that dimension)

<measure:Measure name="Ending Inventory Units" 
     scale="0" type="derived" default="true" styleName="unitStyle">
     <measure:Formula>
          <[CDATA["Ending Inventory Units"="Beginning Inventory
           Units" + Receipts - "Total Demand Units"]]>
     </measure:Formula>
     <measure:SimpleRollUp summaryOperator="last_in_period">
          <measure:Dimension name="Manufacturing"/>
     </measure:SimpleRollUp>
</measure:Measure>

The scope of a cross-cube formula is defined explicitly by the cube map.

In the following example, the cross-cube formula scope applies to the following members:

All members in the Component dimension that fall in the range of the cube mapping functions
Members at the weekly level in the Manufacturing dimension (there is a dense roll-up from week along that dimension)

Note:

In this example, the on-change formula has the same scope as the cross-cube formula.

<measure:Measure name="Schedule Required" hidden="true"
     scale="0" type="derived">
     <measure:SimpleRollUp summaryOperator="sum">
          <measure:Dimension name="Manufacturing">
     </measure:SimpleRollUp>
     <measure:ComplexRollUp>
          <measure:Cube name="Schedule"/>
          <measure:Formula>
               <![CDATA["Schedule Required"=Schedule.required]]>
          </measure:Formula>
          <measure:OnChangeFormula>
              <measure:Measure name="required"/>
              <measure:Formula>
                    <!CDATA["Schedule Required"="Schedule Required"
                    + deltaValue(Schedule.required)]]>
               </measure:Formula>
          </measure:OnChangeFormula>
     </measure:ComplexRollUp>
</measure:measure>

The scope of a roll-up formula along a sparse dimension excludes leaf members.

In the following example, the roll-up formula and the on-change formula for the roll-up are only applicable to Component members at a non-leaf level.

<measure:Measure name="Dependent Demand Units"
     scale="0" type="derived" styleName="unitStyle>
     <measure:ComplexRollUp>
          <measure:Dimension name="Component"/>
          <measure:Formula>
               <![CDATA{"Dependent Demand Units"="Dependent Demand
               Units" + source(Required) * 
               bomScaleFactor(sourceSparseMember(Component))]]>
          </measure:Formula>
          <measure:OnChangeFormula>
               <measure:Measure name="Required"/>
               <measure:Formula>
                    <![CDATA["Dependent Demand Units"=Dependent
                    Demand Units" + deltaValue(source(Required)) * 
                    bomScaleFactor(sourceSparseMember(Component))]]>
               </measure:Formula>
          </measure:OnChangeFormula>
     </measure:ComplexRollUp>
     <measure:SimpleRollUp summaryOperator="sum">
          <measure:Dimension name="Manufacturing"/>
     </measure:SimpleRollUp>
</measure:Measure>

Use startLevel to restrict the roll-up scope.

In the following example, a measure is defined in the Forecast cube. The roll-up formulas are applicable to members in the Geography dimension at the Region level and to the upper levels.

<measure:Measure name="Marketing Forecast Revenue"
     displayName="Marketing Forecast $"
     scale="2" type="loaded" styleName="dollarStyle">
     <measure:DataRowSource name="MktForecast" columnName="REVENUE"/>
     <measure:SimpleRollUp summaryOperator="sum">
          <measure:Dimension name="ProductLine"/>
     </measure:SimpleRollUp>
     <measure:SimpleRollUp summaryOperator="first_in_period">
          <measure:Dimension name="Fiscal"/>
     </measure:SimpleRollUp>
     <measure:ComplexRollUp startLevel="Region">
          <measure:Dimension name="Geography"/>
          <measure:Formula>
               <![CDATA[{"Marketing Forecast Revenue"="Marketing
               Forecast Revenue" + source("Marketing Forecast
               Revenue")]]>
          </measure:Formula>
          <measure:OnChangeFormula>
               <measure:Measure name="Marketing Forecast Revenue"/>
               <measure:Formula>
                    <![CDATA["Marketing Forecast Revenue"="Marketing 
                    Forecast Revenue" + deltaValue
                    (source("Marketing Forecast Revenue"))]]>
               </measure:Formula>
          </measure:OnChangeFormula>
     </measure:ComplexRollUp>
</measure:Measure>

The inference of a roll-down scope is the inverse of a roll-up scope.

<measure:Measure name="Marketing Forecast Revenue"
     displayName="Marketing Forecast $"
     scale="2" type="loaded" styleName="dollarStyle">
     <measure:DataRowSource name="MktForecast" columnName="REVENUE"/>
     <measure:SimpleRollUp summaryOperator="sum">
          <measure:Dimension name="ProductLine"/>
     </measure:SimpleRollUp>
     <measure:SimpleRollUp summaryOperator="first_in_period">
          <measure:Dimension name="Fiscal"/>
     </measure:SimpleRollUp>
     <measure:ComplexRollUp startLevel="Region">
          <measure:Dimension name="Geography"/>
          <measure:Formula>
               <!CDATA["Marketing Forecast Revenue"="Marketing
               Forecast Revenue" + sourc
               ("Marketing Forecast Revenue")]]>
          </measure:Formula>
          <measure:OnChangeFormula>
               <measure:Measure name="Marketing Forecast Revenue"/>
               <measure:Formula>
                    <!CDATA["Marketing Forecast Revenue"="Marketing
                    Forecast Revenue" + deltaValue
                    (source("Marketing Forecast Revenue"))]]>
               </measure:Formula>
          </measure:OnChangeFormula>
     </measure:ComplexRollUp>
</measure:Measure>

Scope operators on sparse dimensions are only on the left hand side of the measure formula.

In the following example, the Loaded Projected ASP measure is restricted by the [level(Month)] operator, and the Backlog Units measure is restricted by the [previous(Fiscal)] operator. The two operators are on the Time dimension, which is dense.

<measure:Measure name="Loaded Projected ASP"
     displayName="Projected ASP"
     scale="2" type="derived">
     <measure:Formula>
          <![CDATA["Loaded Projected ASP"[level(Month)]=ProjectedASP.asp]]>
     </measure:Formula>
</measure:Measure>

<measure:Measure name="Backlog Units"  scale="0"  type="derived">
     <measure:Description>
          Booked units minus shipped units in a given time period
     </measure:Description>
     <measure:Formula>
          <![CDATA[["Backlog Units"="Backlog Units"[previous(Fiscal)]+"Booked Units"-"Shipped Units"]]>
     </measure:Formula>
     <measure:SimpleRollUp summaryOperator="last_in_period">
          <measure:Dimension name="Fiscal"/>
     </measure:SimpleRollUp>
     <measure:SimpleRollUp summaryOperator="sum">
          <measure:Dimension name="Product"/>
     </measure:Dimension name="Product"/>
</measure:Dimension name="Product"/>

Tip:	To avoid ambiguity when using names that could imply multiple objects (dimensions, members, hierarchies, or levels) in a measure formula, use a casting function to convert to the correct type.

In the following example, “dimension(Mode)” converts the name “Mode” to a dimension object.

"Projected Fuel Surcharge" =
if (memberName(dimension(Mode)) == "TRUCK",
 
"Projected Fuel Surcharge Rate",
"Projected Line Haul Rate"
)

Other casting functions include cube, dimensionMember, hierarchy, hierarchy, hierarchyLevel, and location. See Member Functions.