I am trying to create a custom customer profitability report in Analytics.
We have some custom fields and columns on an invoice & invoice line that calculate the values for warehousing / delivery and another that calculates the rebate percentage per item for that customer.
I'm trying to build a dataset that will give me the cogs from the item fulfilment and the revenue from the invoices or credit memos.
Where I am struggling is to calculate the COGs from an item fulfilment in it's own column. When I grab QTY x Transaction line Rate this gives me the values for the item fulfilment as COGs but because I need revenue in it's own column this way isn't working as in an analytics report it is bundling the rate x QTY values from the item fulfilment in with the invoices value.
Does anyone know a way I can split out the COGS from the item fulfilment in it's own column?
I am able to get the revenue from the amount which is a field with values only present on the invoices / credit memos.
I also cannot link a dataset with this dataset to only have COGS details from item fulfilment because I am using another linked dataset from Journals.
Any help is appreciated!~