Grouping Calculations

[Jen]

I’ve created a report in which invoices are grouped by vendor and then by
service period. I’ve calculated the total invoice amount. But I also need
to calculate the average invoice amount per service period. The problem I’m
having is that there aren’t the same number of service period by month
groupings per vendor. Please help.

 

[Marshall Barton]

I’ve created a report in which invoices are grouped by vendor and then by
service period. I’ve calculated the total invoice amount. But I also need
to calculate the average invoice amount per service period. The problem I’m
having is that there aren’t the same number of service period by month
groupings per vendor.

You can count the number of service periods by adding a text
box (named txtServicePeriods) to the service period group
header or footer section. Set its control source expression
to =1 and RunningSum property to Over Group.

Then the vendor group footer text box expression would be:
=Sum([invoice amount field]) / txtServicePeriods

 

[Jen]

Marshall....Thank you so much for this. It worked. I've been trying to get
answers for days, but I guess my posts were too long. At least now I know to
keep my questions short and to the point without going into too much detal.

I’ve created a report in which invoices are grouped by vendor and then by
service period. I’ve calculated the total invoice amount. But I also need
to calculate the average invoice amount per service period. The problem I’m
having is that there aren’t the same number of service period by month
groupings per vendor.

You can count the number of service periods by adding a text
box (named txtServicePeriods) to the service period group
header or footer section. Set its control source expression
to =1 and RunningSum property to Over Group.

Then the vendor group footer text box expression would be:
=Sum([invoice amount field]) / txtServicePeriods