Hi there, I'm currently trying to set up a report that will show me the correct margin by percentage, currently my DB has a column that gives me the margin, but it's the wrong margin.
Example 1.
if a item had been sold 10 times and I get 10 margin for each time that is being sold, my db and crystal report will take all 10 margin and add it all up and divided by 10 and give me the total margin of the item; however, the real margin should be calculated by my total of all 10 Gross profit and all 10 of my sales amount. I talk the total sum of my gross profit and divided the total sum of my sales amount and that is the real margin.
Example 2.
Margin
44.18604651
55.05617978
55.05617978
51.61290323
55.05617978
55.05617978
44.18604651 Total Margin% is 51.46% and is the wrong margin
correct Margin that is being calculated is
Grost profit
171
382.2
411.6
230.4
264.6
720.3
47.5
total of 2227.6
Sales Amount
387
694.2
747.6
446.4
480.6
1308.3
107.5
total of 4171.6
"Gross Profit" 2227.6 / "Sales Amount" 4171.6 = 53.40%
I would like to know how do I go about format the correct formula in my reports, I had already go to my formula field and set up a formula of sum ({gross.profit}) / sum ({sales.amount}) and it didn't work. I had also set up a formula to get my Gross profit and my sales amount within Crystal report than take the 2 formula and / against each and no go. I had also create group and summarize group of my sum total of my gross profit and sales amount and set up formula to divide my to summarize group and no go.
I have over 3000 items within a excel sheet and each item fell after another item so I set up a group against my item code, so the item code wont' repeat it self.