Removing duplicate receipts to obtain average on a guage display

[#1]

Sorry - this is another simple problem I'm stuck with.

Removing all descriptive fields to express the problem as simply as possible we have two sources of data

Stock-Receipts:

Batch#                         Receipt-Date

A1                                15/08/15

A2                                16/08/15

A2                                16/08/15

A3                                17/08/15

Shipments-to-Customers

Batch#                         Ship-Date

A2                                21/08/15

A3                                24/08/15

The guage would display the avrage days taken to ship a product (considering only batches actually shipped) with a dimension like

=num(AVG([Ship-Date] - [Receipt-Date]),'###.#')

My problem is, as per the example above, there may be multiple receipts on the same day (and maybe multiple shipments to customers)

Batch A2 has taken 5 days to ship and batch A3 has taken 7 days to ship so I want an average of 6 days on the guage

but because there are two receipts of A2 the average from num(AVG([Ship-Date] - [Receipt-Date]) is coming as (5  + 5 + 7)/3 = 5.7 instead of (5 + 7)/2 = 6

Somewher i need a DISTINCT but I can't seem to get the right answer

Tks for you help

Dave

[ziadm]

HI

You should have one Fact table by joining the two table ...

LOAD

Batch,

Receipt-Date

FROM

[...\ReceiptTable.xls]

(biff, embedded labels, table is Sheet1$);

Right Join

LOAD

Batch,

Ship-Date

FROM

[...\ShippTable.xls]

(biff, embedded labels, table is Sheet1$);

Having that you should have one Table

Batch                         Ship-Date     Receipt-Date

A2                                21/08/15     16/08/15

A3                                24/08/15     17/08/15

Then you can calculate the average shipping date

[sunny_talwar]

For your sample this should work:

=Avg(Aggr(Avg([Ship-Date] - [Receipt-Date]), Batch#))

[jonathandienst]

If you cannot identify which receipt for A2 ties to which shipment for A2, then I would use:

     =Avg([Ship-Date]) - Avg([Receipt-Date])

     or

     =Min([Ship-Date]) - Max([Receipt-Date])

That assumes that Batch# is a dimension. To get the average across all batches:

     =Avg(Aggr(Avg([Ship-Date]) - Avg([Receipt-Date]), [Batch#])

[#5]

Tsk all - I think all seem to work, but being lazy I took the last reply - tks everyone for your help