I have created a custom record called "Products" that looks to the Customer Record as a Parent. One of the fields on this custom form is named "Fixed Cost" that determines my cost per product.
Example:
Product1 = $100 Fixed Cost
Product2 = $200 Fixed Cost
Prodcut3 = $300 Fixed Cost
Each customer has the potential to have multiple products and multiple fixed cost amounts. I want to sum these amounts as the numerator (from the products custom record) and divide them into the total sales amount (located on the customer record in a custom currency field) to determine my margin % in a Saved Search.
Whenever I attempt this, I have no problems sourcing both numbers on the search, but the amount for the total sales amount is repeated for the line items that represent the products. To explain using the example above:
Product1 = $100 Fixed Cost / $1000 Total Sales $
Product2 = $200 Fixed Cost / $1000 Total Sales $
Product3 = $300 Fixed Cost / $1000 Total Sales $
I want to sum the products ($600) and divide the Total Sales Amount ($1000) to come up with the correct 60% ratio (or 40% margin). The search recognizes the Total Sales Amount from the customer record for each line, in the example it would return $600 cost / $3000 total sales = 20% (80% margin)... this is incorrect.
How can I fix this problem? I tried SUM on the fixed cost and AVERAGE on the Total Sales Amount which gives me the correct individual values for each of these, but will not produce the ratio I need for margin. When I attempt to add a formula(%) field that divides {custrecord_flat_fixed_cost}/{custrecord_cust_products.custentitytotal_sales} and attempt to sum or average, I receive an unexpected error (I think because I am summing the numerator and averaging the denominator).
I would even be open to a custom customer record that sums the product fixed cost field.
Thanks in advance - I have had this issue for a few other searches and could never get this to work out properly.