[KristinAvril Contributions: 0Level 1☆]

Hello,

I am trying to create a search For a sublist that will calculate the average selling price of an item based on Sum of total amount ( on all related sales orders) /total number of units sold.

I am feeling every bit the novice that I am, and have spent way to much time trying to search for the answer.

I have started with a transaction search and got the average of the rate , but not the average of the total units sold.

I know there has to be an easy formula out there that i am missing that will return the result.

Obviously the next step would be the Profit margin % based on that result.
This is a cached copy. Click here to see the original post.

[KCSF Bud Contributions: 0Level 1☆]

Kristin,

Seems pretty straightforward so maybe I am missing something. If you have gotten to the Rate average (using the Average summary grouping), the Quantity should be able to be grouped using the same Average summary type. Be sure to set Main Line = False on your criteria.

The result you are wanting is a list of Items, with columns for average price and average units (quantity), yes?

I think a transaction search of type sales order (or Invoice, if you prefer) with criteria Main Line = F, maybe some item type criteria, then these fields on the results:

Item – Summary Type: Grouped

Rate – Summary Type: Average

Quantity – Summary Type: Average (and also would want to use the Round function or you’ll get a million decimals)

Profit Margin a little more difficult, especially if you want the real (not estimated) profit margin. Might take a little SQL on that part.

Hope that helps.

[KristinAvril Contributions: 0Level 1☆]

Yup, that is where I went too but what this gives me is the average of the rates. Using the table below as an example adding the 4 rates together and dividing by 4. But that is not the correct average because I did not sell 4 units, I sold 5520. I think the whole equation needs to be SQL. And that is where I fall down.

I can get to it from a report and using the total revenue / total Qty sold, but that does not include items not yet invoiced. and I can’t get it to show up on the Item record.

Qty
Rate
Amount

SO1
4020
0.1
402

SO2
60
0.3
18

SO3
1320
0.03
39.6

SO4
120
0.4
480

Sum of units
Average of Rate
Sum of total amount of sale

5520
0.21
939.6

True average selling rate
0.17

Any Ideas would be most welcome.

[KCSF Bud Contributions: 0Level 1☆]

Yes of course, that won’t work. Try a formula field with summary type of average (or I think Max, Min, Sum would do the same): SUM({amount})/SUM({quantity})

[mushrush Contributions: 0Level 1☆]

KCSF Bud your answer above worked for me (partially), thank you! What is happening now is that I get my Average selling price (ASP) by doing this: SUM({amount})/NULLIF(SUM({quantity}),0).

We are a rock quarry and want to know the ASP per ton (qty). That is why I am making sure the qty is not zero.

What is not calculating correct is the Total line on the summary results page. I am getting 1.00. (for my current results set, it should be 10.25)

My summary type for this formula column is Average. I’ve tried Sum as well, it gets me the sum of all my formula column (not what I want either)

Any help is greatly appreciated.

[KCSF Bud Contributions: 0Level 1☆]

What are you grouping by on the results tab? I don’t think this will work for each row and also the grand total at the bottom. It does work for me for different groupings of the rows (say, by item or by document number, date). Depending on how you are trying to view the date, I bet you could group it by something that works? I don’t know that I can explain why the grand total part doesn’t work, other than its probably in the math of averaging an average….

[mushrush Contributions: 0Level 1☆]

I am grouping by a custom field we created on the Transaction call “end market”. Image below.

Regarding the Avg of an Avg; I was thinking the same thing. I have submitted a case to NS Support about this to see if they know a solution as well.

My rows calculate correctly, it is just the total at the bottom…

[Author]

Posts