Lookup - Same Module

Scenario 1

When Units and Price have the same level of detail, you can use Lookup to calculate Sales.

• Generally, metrics are derived based on individual drivers.

• Gross Sales is a metric calculated from individual drivers such as Units Sold and Average Price.

• Gross Sales = Units Sold * Average Price

• Gross Sales is a formula node with the above formula, using input nodes: Units Sold and Average Price.

• formula can use multiple nodes with a combination of mathematical operands like *, /, +, -.

Sample formulas can be:

Scenario details

• Formula to calculate: Gross Sales = Units Sold * Average Price

• Units Sold and Average Price are captured/maintained at the same level of detail: by account, by product, and by customer.

• A module is defined for the input template to maintain both input node values: Gross Sales.

• After calculation, the result will be posted to the same level of detail: by account, by product, and by customer.

Input Node

• Node1: Units Sold
• Module: Module Gross Sales created with the following definition:
  • Rows: account, product, customer
  • Columns: time
  • Filter: version
• Relation to be used in Formula: Merge

• Node2: Average Price
• Module: The same module, Gross Sales, can be used as the template since the level of detail is the same.
  • Rows: account, product, customer
  • Columns: time
  • Filter: version
• Relation to be used in Formula: Lookup
• Lookup Metric: Units Sold

Expected Output Node

• Formula Node: Gross Sales
• Module: The same module, Gross Sales, can be used as the template since the level of detail is the same.
  • Rows: account, product, customer
  • Columns: time
  • Filter: version
• Formula to be used: 'Units Sold' * 'Average Price'

Create Formula

There are two parts to creating the Formula node in the Value Driver tree.

Create Value driver tree

1. To create a new value driver tree, Click on Drive menu.

2. Under Calculations, click the Plus icon and select Rules to open popup for capturing value driver details:

   • Description: Gross Sales Calculation

   • Action Type: Select Value Driver

   • Model: Revenue Planning

     

3. Click on + Add Rule on the level 2 header to open the node creation pane:

   General Tab:

   • Name: Gross_Sales

   • Description: Gross Sales

   • Method: Formula

   • Module: Gross Sales

   • Formula: 'UnitsSold' * 'AvgSalesPrice'

4. Select the field member tagging to post the result of the calculation.

5. Click on the filter icon on the right of each field and make relevant selections:

   • Customers: Same as Source

   • Products: Same as Source

   • Company: Same as Source

   • Version: Same as Source

   • Time: Same as Source

   • Revenue Account: R10100 - Gross Sales

     Note: By default, the output is posted as "Same as source", even if left unselected for fields.

     

Input Tab

1. Click on Input Tab and open node: UnitsSold:

   Module: Gross Sales

   Relation: Merge

   Fields member selection: Click on the filter button on the right of each field:

   • Company: Context Member

   • Version: Context Member

   • Time: Context Member

   • Revenue Account: R10010 - Units Sold

2. Next on the Input Tab itself, open Input node AvgSalesPrice:

   Module: Gross Sales

   Relation: Lookup

   Lookup Metric: UnitsSold

   Fields member selection: Click on the filter button on the right of each field:

   • Company: Context Member

   • Version: Context Member

   • Time: Context Member

   • Revenue Account: R10020 - Avg Sales Price