cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlNixon
Helper III
Helper III

Calculating an average value based on partial collection

Hi superusers,

 

Looking for some help calculating

 

I have the following table/collection

 

Order Nr

DateQtySelling PriceTotal Price
130-Jun-21100 $         5.00 $     500.00
215-Jun-2150 $         6.00 $     300.00
37-Jun-2175 $         7.00 $     525.00
42-Jun-2125 $         7.50 $     187.50
515-May-21100 $         5.00 $     500.00
67-May-21150 $         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

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Community Support
Community Support

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),"#.##")

valbaimsft_1-1624517108590.png

Best regards,

Allen

View solution in original post

1 REPLY 1
v-albai-msft
Community Support
Community Support

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),"#.##")

valbaimsft_1-1624517108590.png

Best regards,

Allen

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,523)