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

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,828)