I'm having trouble coming up with the profit percentage on an Invoice table. Here is what I have:

INVOICE::total - a number field that shows the sum of the invoice line items amount.

INVOICE::cost - a number field that shows the sum of the invoice line items cost.

INVOICE::profitPercent - a calculation field, returns a number: (total-cost) / total.

These work properly. I also have the following summary fields:

INVOICE::total_total - a summary field, Total of total

INVOICE::cost_total - a summary field, Total of cost

These are in the subsummary part and are also working properly.

Now I want to find the profit percentage from the subsummary fields. I put the INVOICE::profitPercent field in the sub-summary area but that didn't work. Then, I created another field:

INVOICE::profitPercentAvg - a summary field, Average of INVOICE::profitPercent. This is close but not exact. I've included two screenshots below. The biggest discrepancy is the first one where the percentage should be 63.73% but instead shows 68.75%

Can anyone shed any light on what I might be doing wrong?

Hey Dan,

I think what you want to do is use the GetSummary function, which allows you to use the value of summary fields in a calculation. So you'd have something like:

(GetSummary(total_total ; breakfield) - GetSummary(cost_total ; breakfield)) / GetSummary(total_total ; breakfield).

The breakfield is the field used for that subsummary part. If it's a Trailing Grand Summary part, use the same field as the first and second part of the GetSummary argument (e.g., GetSummary(total_total ; total_total) - GetSummary(cost_total ; cost_total).

Gordon Shewach

Desktop Services

Ann Arbor, MI