hello everyone , i want the discount product to be subtracted from margin in the sales pivot report
for example :
I sold three items each item's price is 10 $ and the manufacturing cost is 5 $, so the total is 30 $ and i made a discount for 10% on each item so the price will be 27 $ so the margin in the sales report should be 12 $ after the discount (-3$ ) but the report shows me that the margin is = 15 $, so the discount didn't calculate in the margin in a proper way, so how to solve this problem?
ANSWER : the query of pivot table margin field :- SUM(l.price_subtotal - coalesce (l.total_cost,0) / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS margin