Complex GI Scenario

Hi,

We are building a complicated GI scenario related to a custom margin calculation and looking for some help. Here’s the scenario:

We sell item A (stock item) and upgrades associated with item A (non-stock item we will call item B). We have a pricing tool that provides a suggested price (we call this MSRP) - on the Sales Order we have created a UDF that our team enters the MSRP. We are looking to measure our Sales team performance by calculating the % difference between the unit price on item A and item B against MSRP. Here is a simple example:

SO0000012 

• MSRP (suggested price) of $5000
• Unit Price Item A $4500
• Unit Price Item B $1500

In this scenario, the math would be as follows:

((Total Price of A + B)/(MSRP)-1) x 100

((4500+1500)/5000)-1)x100

20% - This would be a favorable margin by the sales rep.

I am building out a GI to perform this calculation and I am very close but hitting a roadblock. Here is what I have done so far:

1. We have mapped the UDF - MSRP field on the Sales Order to the actual inventory Item A MSRP field within stock item screen. This allows us to use this field as a numeric field and perform various calculations on it.
2. Joined SOtable with SOLine table with InventoryItem table with InventoryItemCurySettings table - all inner joins.
3. Added conditions so we are only looking at Item A and Item B (we often have additional items on Sales Order that must be omitted from this calculation). Added condition to InventoryItemCurySettings.RecPrice does not equal 0. This is the MSRP field within the stock item screen. This condition is added so we do not receive error dividing by zero.
4. We are grouping by Sales Order #.
5. On Results, we are looking at:
   1. SO#
   2. InventoryItemCurySetting.RecPrice (MSRP) - Aggregate function of MAX
   3. SOLine.CuryUnitPrice - Aggregate function of SUM
   4. The following calculated field which is attempting to perform calculation per above scenario:
      1. (((SOLine.CuryUnitPrice)/InventoryItemCurySetting.RecPrice)-1)*100

It is this formula that is not providing the correct values for Sales Orders which include Item B. I have simplified this field for the purpose of troubleshooting to SOLine.CuryUnitPrice/InventoryItemCurySetting.RecPrice

For orders where Item B is included, the formula above seems to be defaulting to the Item B unit price & item B rec price (MSRP). In my scenario, I’m wanting to use the sum of item A+B for calculating price (SOLine.CuryUnitPrice) but wanting to use item A for the MSRP (InventoryItemCurySetting.RecPrice. Since I am using the grouping, how does the system decide to pull these details for which inventory ID in a formula such as the one above?

Am I able to hardcode the formula so it pulls the data from a specific inventory ID while using the grouping function?