Calculating an average value based on partial collection

[KarlNixon]

Hi superusers,

Looking for some help calculating

I have the following table/collection

Order Nr	Date	Qty	Selling Price	Total Price
1	30-Jun-21	100	$         5.00	$     500.00
2	15-Jun-21	50	$         6.00	$     300.00
3	7-Jun-21	75	$         7.00	$     525.00
4	2-Jun-21	25	$         7.50	$     187.50
5	15-May-21	100	$         5.00	$     500.00
6	7-May-21	150	$         4.00	$     600.00

I need to calculate the average selling price of the most recent sales of 300 items (top 4 rows and 1/2 of 5th row)

In other words

=((100*$5.00)+(50*$6.00)+(75*$7.00)+(25*$7.50)+(50*$5.00))/300 = $5.88

Any assistance greatly appreciated

[v-albai-msft · Accepted solution]

Hi @KarlNixon .

You can use this formula to get the total price of these 300 items:

Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2

You can use this formula to get the number of items(the result is 300):

Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2

Then you can use this formula to get the average selling price:

(Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2)

If you want to make the result into a 2 decimal numbers, you can use this formula:

Text(XXX,"#.##")

Text((Sum(FirstN(collection1,5),Price)-Last(FirstN(collection1,5)).Price/2)/(Sum(FirstN(collection1,5),Qty)-Last(FirstN(collection1,5)).Qty/2),"#.##")

Best regards,

Allen