Count Distinct based on another field

[gardan]

Here is a sample of my data:

DeliveryID,Origin,Destination,ProductID,Quantity,Distance
33613333,US66,6100456,4055,540,40
33613344,US44,6100589,4055,240,84
33613344,US44,6100589,4003,300,84
33613355,USKL,6100666,4055,540,32
33613377,USPP,6100788,4003,250,76
33613377,USPP,6100788,4055,290,76

The first Delivery had 540 of Product 4055 on a truck that went 40 miles.

The second delivery had 240 of product 4055 and 300 of product 4003 on the truck that went 84 miles.

Now to count the number of loads, I can write the simple expression: Count(DISTINCT DeliveryID) and the answer would be 4

My question is how is the best way to calculate the average distance the 4 deliveries ran.

If my expression is Avg(Distance) would it not weight those deliveries that delivered two diffent products twice as much as the ones that delivered one product?

[#2 · Accepted solution]

you could use the aggr function like so:

aggr(avg(Distance), DeliveryID)

Which would return 58 (the average of the 4 unique trips) but you would also need to change your charts total mode to "average of rows" instead of expression total

[gardan]

I never thought of using the aggr() function. thanks!!