Identifying Inventory Quantity and Calculating Gross Margin in Dynamics SL Database

[GuackGuack]

Currently, I only have access to the Dynamics SL database, and I need to identify the available inventory quantity for each product to calculate the gross margin. I have reviewed the main inventory tables such as ItemSite, INTran, LotSerMst, and other related tables, but I have found discrepancies between the recorded quantities and the transactions. For instance, the quantities in ItemSite.QtyOnHand often do not match the calculations derived from the transactions in INTran.

Additionally, some records in the IN10990_ItemSite table are empty or do not reflect the expected data. Furthermore, the transactions have different TranType values (such as RC, IS, II, etc.), which complicates identifying the inventory movements.

My goal is to calculate the available inventory quantity for each product (InvtID) by site (SiteID), as well as determine the gross margin using the costs (ItemCost) and sales prices (SlsPrc). In which table and field can I find the definitive data for the available inventory quantity for each product, and how can I ensure that I am using the correct information to accurately calculate the gross margin?

[Anonymous]

Hi @GuackGuack ,

1)Identifying Available Inventory Quantity.

To calculate the available inventory quantity, I would suggest creating a calculated column or measure that sums the net quantity for each product (InvtID) and site (SiteID) by grouping the transactions. This method will allow you to account for different transaction types and provide a more accurate view of inventory levels.

IdentifyingInventoryQuantity = SUMX(FILTER(INTran,INTran[TranType] = "RC" || INTran[TranType] = "II"),INTran[Qty]) -
SUMX(FILTER(INTran,INTran[TranType] = "IS"),INTran[Qty])

2)Calculating Gross Margin

For calculating gross margin, you may want to use the following DAX formula. It subtracts the item cost from the sales price and divides the result by the sales price to calculate the percentage. This approach ensures that you handle cases where the sales price might be zero without resulting in errors.

GrossMargin = (SalesOrder[SlsPrc] - ItemCost[ItemCost])/SalesOrder[SlsPrc]

Best Regards,

Bof