Calculations in Report Footer

[R Marko]

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[Duane Hookom]

I'm not sure what language uses [ ] rather than ( ) for the Sum() function.
You can't aggregate controls from one section to another. You could use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

 

[R Marko]

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in the
footer (62.5%) rather than $33.33%.

Any insights?

I'm not sure what language uses [ ] rather than ( ) for the Sum() function.
You can't aggregate controls from one section to another. You could use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[Duane Hookom]

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])


--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in the
footer (62.5%) rather than $33.33%.

Any insights?

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[R Marko]

Your a genius....It works for the "Monthly Footer" but doesn't work in the
"Report Footer" What adjustment do I need to make?

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in the
footer (62.5%) rather than $33.33%.

Any insights?

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[Duane Hookom]

"doesn't work"... I'm having trouble seeing your results.
If [InvoiceAmount], [ClientComm], [Expense], and [InvoiceAmount] are all
fields in your report's record source, you should not have troubles. Are you
sure your Report Footer is not the Page Footer?


--
Duane Hookom
MS Access MVP
--

Your a genius....It works for the "Monthly Footer" but doesn't work in the
"Report Footer" What adjustment do I need to make?

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in the
footer (62.5%) rather than $33.33%.

Any insights?

:

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could
use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[R Marko]

I went back and checked. I have the exact same calculations in the:
Monthly Footer
Year to Date Footer
Report Footer

In the Monthly Footer, the percentage calculates perfectly
In the Report Footer, the percentage calculates perfectly

In the Year to Date Footer, All columns calculate perfectly because they are
all "Sum" calculations. The percentage, however, should not be a sum of all.
It's a percentage that should be calculated just as it is in the "detail"
section.

How do I make that happen?

"doesn't work"... I'm having trouble seeing your results.
If [InvoiceAmount], [ClientComm], [Expense], and [InvoiceAmount] are all
fields in your report's record source, you should not have troubles. Are you
sure your Report Footer is not the Page Footer?


--
Duane Hookom
MS Access MVP
--

Your a genius....It works for the "Monthly Footer" but doesn't work in the
"Report Footer" What adjustment do I need to make?

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in the
footer (62.5%) rather than $33.33%.

Any insights?

:

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could
use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4 is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[Duane Hookom]

So, what is the calculation you want want in the ytd footer? What are the
scopes of the values?

--
Duane Hookom
MS Access MVP
--

I went back and checked. I have the exact same calculations in the:
Monthly Footer
Year to Date Footer
Report Footer

In the Monthly Footer, the percentage calculates perfectly
In the Report Footer, the percentage calculates perfectly

In the Year to Date Footer, All columns calculate perfectly because they
are
all "Sum" calculations. The percentage, however, should not be a sum of
all.
It's a percentage that should be calculated just as it is in the "detail"
section.

How do I make that happen?

"doesn't work"... I'm having trouble seeing your results.
If [InvoiceAmount], [ClientComm], [Expense], and [InvoiceAmount] are all
fields in your report's record source, you should not have troubles. Are
you
sure your Report Footer is not the Page Footer?


--
Duane Hookom
MS Access MVP
--

Your a genius....It works for the "Monthly Footer" but doesn't work in
the
"Report Footer" What adjustment do I need to make?

:

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])


--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in
the
footer (62.5%) rather than $33.33%.

Any insights?

:

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could
use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4
is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[R Marko]

I want to take the sum of the InvoiceAmount
Deduct the sum of the ClientComm
Deduct the sum of the CGS
and divide that amount by invoiceAmount

This will give me a percentage of profit.

Rhonda

So, what is the calculation you want want in the ytd footer? What are the
scopes of the values?

--
Duane Hookom
MS Access MVP
--

I went back and checked. I have the exact same calculations in the:
Monthly Footer
Year to Date Footer
Report Footer

In the Monthly Footer, the percentage calculates perfectly
In the Report Footer, the percentage calculates perfectly

In the Year to Date Footer, All columns calculate perfectly because they
are
all "Sum" calculations. The percentage, however, should not be a sum of
all.
It's a percentage that should be calculated just as it is in the "detail"
section.

How do I make that happen?

"doesn't work"... I'm having trouble seeing your results.
If [InvoiceAmount], [ClientComm], [Expense], and [InvoiceAmount] are all
fields in your report's record source, you should not have troubles. Are
you
sure your Report Footer is not the Page Footer?


--
Duane Hookom
MS Access MVP
--

Your a genius....It works for the "Monthly Footer" but doesn't work in
the
"Report Footer" What adjustment do I need to make?

:

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])


--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5% in
the
footer (62.5%) rather than $33.33%.

Any insights?

:

I'm not sure what language uses [ ] rather than ( ) for the Sum()
function.
You can't aggregate controls from one section to another. You could
use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column 4
is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko

 

[Duane Hookom]

What's wrong with:
=(Sum([InvoiceAmt]) - (Sum([ClientComm) + Sum([CGS])))/[InvoiceAmount]

--
Duane Hookom
MS Access MVP

I want to take the sum of the InvoiceAmount
Deduct the sum of the ClientComm
Deduct the sum of the CGS
and divide that amount by invoiceAmount

This will give me a percentage of profit.

Rhonda

So, what is the calculation you want want in the ytd footer? What are the
scopes of the values?

--
Duane Hookom
MS Access MVP
--

I went back and checked. I have the exact same calculations in the:
Monthly Footer
Year to Date Footer
Report Footer

In the Monthly Footer, the percentage calculates perfectly
In the Report Footer, the percentage calculates perfectly

In the Year to Date Footer, All columns calculate perfectly because
they
are
all "Sum" calculations. The percentage, however, should not be a sum
of
all.
It's a percentage that should be calculated just as it is in the
"detail"
section.

How do I make that happen?

:

"doesn't work"... I'm having trouble seeing your results.
If [InvoiceAmount], [ClientComm], [Expense], and [InvoiceAmount] are
all
fields in your report's record source, you should not have troubles.
Are
you
sure your Report Footer is not the Page Footer?


--
Duane Hookom
MS Access MVP
--

Your a genius....It works for the "Monthly Footer" but doesn't work
in
the
"Report Footer" What adjustment do I need to make?

:

Try some stuff.
=Sum([InvoiceAmount]-[ClientComm]-[Expense])/Sum([InvoiceAmount])


--
Duane Hookom
MS Access MVP
--

The final figure is a percent....i.e.
Invoice Expense Profit Profit Percent
$100.00 - $75.00 = $25.00 / $100.00 = 25%
$200.00 - $125.00 = $75.00 / $200.00 = 37.5%
What I'm looking to appear in the footer is:
$300.00 - $200.00 = $100.00 / $300.00 = 33.33%

When I use the formula you provided, it adds the $25% and 37.5%
in
the
footer (62.5%) rather than $33.33%.

Any insights?

:

I'm not sure what language uses [ ] rather than ( ) for the
Sum()
function.
You can't aggregate controls from one section to another. You
could
use a
control source in a report or group footer (or header) like:
=Sum([InvoiceAmt]) - Sum([Expenses])
You should also be able to sum
=Sum(([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount])

--
Duane Hookom
MS Access MVP
--

I have a report that calculates revenue and percentage of
profit.
Column1 contains InvoiceAmt
Column2 contains Expenses
Column3 contains Profit (difference between Inv and Exp)
Column 4 contains the a field [ProfitPerc] which contains the
following:
([InvoiceAmount]-[ClientComm]-[Expense])/[InvoiceAmount]

The report footer contains the following fields

Column 1 Column 2 Column 3
=sum[InvoiceAmt] =sum[Expenses] =sum[Profit]

What I would like to calculate in the report footer in Column
4
is

InvoiceAmt Total minus Expense Total divided by InvoiceAmt
Total
to give me the total Profit Percent

Any help would be appreciated,
Rhonda Marko