# Lookup - Same Module

### Scenario 1

 Use  Lookup to calculate **Sales** from **Units & Price,** when Units & price have the same level of details

* Generally, metrics are derived based on Individual drivers 
* Gross sales is a metric calculated from Individual drivers such as Units Sold & Average price.
*  ***Gross Sales = Units Sold \* Average Price***
* *Gross Sales* is *Formula* node with the above formula with *Input* nodes- *Units Sold & Average Price* 
* Formula can use multiple nodes with the combination of mathematical operands like **\*, /, +, -**

*sample formulas can be -* 

| Formula             | Details                                 |
| ------------------- | --------------------------------------- |
| A \* B              | A & B are Input or formula nodes        |
| A \* B \* C         | A,B, C can be formula or Input nodes    |
| (A + B ) \* C       | A,B, C can be formula or Input nodes    |
| (A \* B) /C         | A,B, C can be formula or Input nodes    |
| (A \* B) / (C \* D) | A,B, C, D can be formula or Input nodes |
|                     |                                         |

**Scenario details**

* Formula to calculate:  *Gross Sales = Units Sold \* Average Price*
* *Units Sold & Average Price* are captured/maintained at the same level of details -
  * by account, by product & by customer
* Module defined for Input template to maintain both the Input node values - **Gross Sales**
* After calculation, the result will be posted to the same level of details 
  * by account, by product & by customer

**Input Node** 

* Node1 - Units Sold 
* Module - Module **Gross Sales** created with the below definition
  * (rows = account, product, customer, columns = time, filter = version)
* Relation to be used in Formula = **Merge**

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2FTHh0gbo5uk0ljupNCM5u%2Fimage.png?alt=media&#x26;token=03b334a6-807a-43f8-89e3-48177280f0d8" alt=""><figcaption></figcaption></figure>

* Node2 - Average Price
* Module - The same module **Gross Sales** can be used as the template & level of details are the same.
  * (rows = account, product, customer, columns = time, filter = version)
* Relation to be used in Formula = **Lookup**
* *Lookup Metric*  **- Units Sold**

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2FpqMEGjatMhBlnZKzW5p9%2Fimage.png?alt=media&#x26;token=206bb29b-6d3f-4ad6-b085-b0ae743af356" alt=""><figcaption></figcaption></figure>

**Expected Output Node**

* Formula Node - Gross Sales
* Module - The same module **Gross Sales** can be used as the template & level of details are the same.
  * (rows = account, product, customer, columns = time, filter = version)
* Formula to be used = **'Units Sold' \* 'Average Price'**

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2FiIviD4kgJ5iJq8GivbFK%2Fimage.png?alt=media&#x26;token=f67cffd0-c55d-4879-9389-2c088567265e" alt=""><figcaption></figcaption></figure>

### Create Formula&#x20;

There are 2 parts of creating the Formula node in Value Driver tree

Part 1 - Create Value driver tree

**Step 1:** To create a new value driver tree, Click on *Drive* menu

* Under Calculations, click on **+** & select *Rules* to Open popup for capturing value driver details
  * *Description* - Gross Sales Calculation&#x20;
  * *Action Type  -* Select *Value Driver*
  * *Model -* Revenue Planning

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2Fgd5ulzYzaRIqgV6IJKou%2FVDT%20Access.png?alt=media&#x26;token=1fc7830d-f455-4718-8a02-97f1fc316b7f" alt=""><figcaption></figcaption></figure>

**Step 2:** 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'

**Step 3:** Select the field member tagging to post the result of the calculation

Click on the filter Icon on the right of the each of fields 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 put is posted as "Same as source", even if left not selected for fields.

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2FlUhzqDD4ZIWZutpteIcZ%2FVDT%20Navigate%20Method%20real.png?alt=media&#x26;token=0f31a355-00bf-40e6-9bd1-1c81db1afe31" alt=""><figcaption></figcaption></figure>

### Input Tab

**Step 4:** Click on Input Tab and Open node - *UnitsSold*

* *Module:* Gross Sales
* *Relation:* Merge&#x20;
* Fields member selection: Click on the filter button on the right of each field&#x20;
  * *Company*: Context Member
  * *Version*: Context Member
  * *Time*: Context Member
  * *Revenue Account*: R10010 - Units Sold

**Step 5:**  Next on 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&#x20;
  * *Company*: Context Member
  * *Version*: Context Member
  * *Time*: Context Member
  * *Revenue Account*: R10020 - Avg Sales Price

<figure><img src="https://3809087323-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCbCZazP0PcAHFLGdNy23%2Fuploads%2FraU2ghyOf9rmAZQRGoc4%2FVDT%20Look%20up%20same.png?alt=media&#x26;token=64580c9e-ac8a-4f33-bb3d-4089b4e487db" alt=""><figcaption></figcaption></figure>