Averages in a Crosstab

[kwalters110]

Hi all,

I'm using Crystal 8.5

I am making a report that is grouped by Customer, then by Part. Each part shows a crosstab and a chart of the average price, cost, and % margin for the month. I have checked these values and everything is fine here. The problem exists when I try to show a summary for the customer.

In the Customer Header I have a crosstab that shows the Total Revenue, Total Cost, and Average Margin. The Columns are the date of the shipped order (grouped by month), and the part number is the row field. Here is a sample of what part XYZ would look like for December of 2004:

Dec '04
... ... ...
XYZ $29,435
$13,868
53%
... ... ...
Totals $132,860
$67,158
46%

The first number is a sum of Revenue, the second is a sum of cost, and the third is an average of margin. Now for a specific part for a specific month, the numbers are pretty accurate.
(29435 - 13868) / 29435 is approx. 53%

But the average margin in the bottom total is wrong for all of my months. Here you see 46% but it should really be 49%. I don't understand why this particular total is wrong. I have taken each margin for Dec '04 and averaged those myself and they came out to 49% so I do not know where 46% is coming from. Any ideas? Thanks in advance for any help.

 

[lbass]

How did you create the percentages (since this isn't a standard option in an 8.5 crosstab)? You should get the correct percentage if you do the following. First create a formula {@percent}:

if {table.cost} > 0 then
({table.revenue}-{table.cost}) % {table.revenue} else 0

Then insert this as your summary field and select "weighted average" as your summary. Choose {table.revenue} as the field to weight by. This should give you the correct values.

Highlight the summary and click on the % icon to add the sign.

This is an adaptation of a solution suggested by Shelby55 in an earlier thread (thread149-769395) and explained in depth by Ken Hamady in one of his newsletters.

-LB