In FDI using a custom subject area, how do I perform detail calculations at a record level before doing aggregation?

We have customer shipments data; one line per shipment where the status of the shipment line can indicate that the item was shipped or the item was returned. Based on shipment line status and shipment date or return date we need to accumulate net sales. So for each shipment line the value of the line can be positive or negative depending on whether the line status is shipped or returned and then whether the ship date or the return date falls within the reporting period.

Currently the aggregation of the data is occurring first, and then the determination of whether to treat the data as a shipment or return is executed. The values are all summed up first. Instead of netting sales and returns all sales and returns are summed as one number. Which is incorrect.

How best to deal with this within the subject area? Can this be dealt with in the subject area? Or should we build a custom dataset and do the calculations in the dataset build before attempting to create a workbook?

Appreciate any and all help.

Paul