Formula in a Query

[spyldbrat]

I am trying to determined the gross profit $ and gross profit% in a query. I was able to create a calculation to determine the dollars, but that calculation I did the % is not calculating correctly. I used the following formula: ([Bill Amount]-[Pay Amount])/[GP Dollars] Can someone tell me the correct calculation?



Thank you.

[isladogs]

Lets say your cost price = $15 and your selling price = $20 then your profit = $5 and your % profit = 100*(20-15)/15 = 33.3%

I'll leave you to convert that to a general formula
HTH

[spyldbrat]

When I try to run the Query, I am getting the following error message: Your query does not include the specified expression '100*([Bill Amount]-[Pay Rate])/[Pay Rate]' as part of an aggregate function?

Below is the formula:
GP Per:100*([Bill Amount]-[Pay Rate])/[Pay Rate]

[spyldbrat]

Disregard my previous post -- I got it to run without the error but the % is still showing the wrong amount:

The GP $ is 98,628.52
The Pay Amount is 364,259.51
GP % is 14,8429.70%

it should show 21.31 %

This the formula I used:

GP Per: Sum(100*([Bill Amount]-[Pay Amount])/[Pay Amount])

[Minty]

You're summing the percentage - I don't think you should be ?

[spyldbrat]

If I remove the sum, I get an expression error. What should I put in in it's place?

[isladogs]

If you are formatting your query field as a %, you should omit the 100 multiplier
Agree that sum isn't required

I don't understand how you get the answer to be 21.31%
The reason I didn't give a generic formula with field names is that I found your names confusing...and still do.