Skip to Content
0
Former Member
Apr 21, 2010 at 05:37 PM

Format and formula

66 Views

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.