Hello - I need some help building my model and connecting the sales order/works order/COGS cost tables.
I have Sales order data (USA Data) which references a works order number.
A works order detail table (WO Detail) that connects works order references to the consignment number NB. mostly there is one WO to one cons. no. but occasionally there are multiple consignments.
A consignment cost table (Consignment Cost Detail) that gives me the COGS which relate to the sales orders from the first table.
I don’t know how to write the measures or calculated columns to connect the data so I can create a report that shows sales, cogs and profit in a dynamic way and allow filters by date or customer in the normal way.
I have attached a pbix file showing an example SO/WO/COns scenario.
Very many thanks for your help, Lizzie
AMCNA Gross Margin.pbix (1.3 MB)