Crystal Reports - Formula to count groups/How to create an average based on sums

I am working on a report that shows Part Numbers with multiple Purchase Order numbers, Order Quantity and Shipped Quantity. First I grouped Part Numbers.

Part # 21104-2F    PO # S7CEO    Order Qty: 10    Shipped Qty: 0
                   PO # S7CEO    Order Qty: 10    Shipped Qty: 0
                   PO # S8LVU    Order Qty: 5     Shipped Qty: 0

Sometimes we split jobs within a purchase order. For example: (look above at PO # S7CEO) you order 20 of part # 21004-2F. We separate it into two lines (or jobs) of 10 parts per job. In this report I only want to see that you ordered 20. To achieve this, I created a second group for Purc开发者_运维问答hase Order numbers and inserted a sum so that way I only see that you order 20 parts.

Part # 21104-2F    PO # S7CEO    Order Qty: 20    Shipped Qty: 0
                   PO # S8LVU    Order Qty: 5     Shipped Qty: 0

My dilemma: I need to show an average based on how many parts you ordered (so average of the 20 parts, not the 10) but CR is giving me an average based on the 10 parts.

How do I create an average based on sums?

I tried using a formula to calculate the sum then divide by line count. There are 3 “lines” but once I grouped and inserted a summary, there are 2 different purchase orders. The line count formula is counting "3" lines. I want it to count how many groups I have so it will come up with "2". Then I will be able to divide "groupcount" (2) by total order qty and that will give me an average. Is there a formula that will help me achieve this?

Thank you in advance!

Yup, Count counts all the records. Use DistinctCount, which counts the values instead:

    Sum({OrderQty})/DistinctCount({PO})

That should give you 2 instead of 3 in the denominator.

As long as you're outputting the data into the group footer, you can use a SUM(shipped quantity)/COUNT(PO #) to get the average parts/shipment.

e.g.

Two level grouping on Part # (group #1) and PO # (group #2). You'd output the actual data in the Group Footer #2 field. To do your average, you'd add two summary values in that footer: SUM(shipped quantity) and COUNT(PO #). Then a simple formula field to do SUM/COUNT of those two values, and you've got your average parts per shipment value.