# Calculations on summary fields in a report

This topic is 2356 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

People like using cats in this forum and it keeps it simple. Let's say I have 3 types of cat (C) - Moggy (M), White (W) and Siamese (S). I am recording prices and using these prices for a report which produces their average price. I want to work out the premium that one breed of cat has over another expressed as a percentage of, say Moggies.

I have 3 prices for Moggies: \$20,\$21,\$22; 3 prices for White cats \$30,\$31,\$32 and 3 prices for Siamese - \$50, \$55, \$60.

My nice summary report produced from a script shows the average prices OK but how can I work out the premium for the other breeds given the calculation has to be on a value which is already a summary?

Cat M Av price \$21

Cat W Av price \$32 Premium \$XX? (calc is (32-21)/21*100)

Cat S Av price \$55 Premium \$XX?

The average is worked out as a weighted average (just in case that is part of the solution)

##### Share on other sites

This is not simple, because while you can group records by category and calculate the average of each group, there is no built-in mechanism by which one group can access the sub-summary values of other groups.

There are several ways around this obstacle - I will mention two:

1. Work this out across a relationship; create a table of Types (3 records in your example), use a calculation field to get the average of related records, a summary field to get the minimum of the averages, and another calculation field to calculate the premium from that. Note that this method will consider all related records, not just the found set.

2. Run a script prior to producing your report to determine the lowest group average and store it in a global field or variable, to be used later by the calculation field that calculates the premium.

31 minutes ago, enquirerfm said:

The average is worked out as a weighted average (just in case that is part of the solution)

That's kind of meaningless when you don't tell us what is it weighted by.

##### Share on other sites

Thanks - this was a helpful start. I have gone for (1) and I am obtaining the results I need.

I wanted to obtain an average which was weighted by the quantity - this is proving more difficult to do. However, just needed to get my mind around all of my fields and managed it... Thanks again.

Edited by enquirerfm
Finalisation
##### Share on other sites

This topic is 2356 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

• ### Similar Content

• #### Count list of values from Summary list field

By Sinky,

• 9 replies
• 328 views
• #### Can I Exclude Duplicates from a Running Total Count?

By Peterteneldas,

• 5 replies
• 965 views
• #### Calculating percentages using summary fields

By -dp-,

• 6 replies
• 938 views
• #### Hiding when Summary Field is empty or zero

By bcooney,

• 3 replies
• 1,327 views
• #### Report number of fields within a date range

By Matthew Bloomfield,

• 5 replies
• 973 views
×
×
• Create New...

## Important Information

By using this site, you agree to our Terms of Use.