0

Nest a SUM formula

I am trying to create a report that shows the turnover and profit by orderID.

I have a report that creates a list of orders for the relevant time period

=NL("Rows","FilteredSalesOrder","salesorderid","createdon",NP("DateFilter",StartDate,EndDate),"+accountidname","*")

From there I have columns for:

Company Name: =NL("First","FilteredSalesOrder","accountidname","salesorderid",C21)

&

Turnover: =NL("First","FilteredSalesOrderDetail","baseamount","Link=FilteredSalesOrderDetail","FilteredSalesOrder","salesorderid","=SalesOrderId","salesorderid",C21,"Link=FilteredSalesOrderDetail","FilteredProduct","productid","=ProductId","new_corebusiness","Cyber Essentials|Penetration Testing|PCI QSA|Training")

My challenge is that I need to have a column for Margin but the standard form in CRM only totals Revenue in Baseamount. What I need to do is

Quantity x (Item Sale Price - Item Cost Price)

Anyone know the simplest way to resolve this one?

Regards

Dave

 

1 comment

Please sign in to leave a comment.