Linking Fact Tables

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)

Hi @LizzieTompsett

Could you try to reduce your model to be able to focus on what is important to solve the problem?.
My first impression is that you could use virtual relationships or bidirectional filtering to perform your calculations.
However, I am not sure about the granularity of your tables since they are full of abbreviations and possibly, unnecessary columns.
Kind regards,
Diego

Hi Diego
Thanks for looking at this for me, attached is slimmed down version of the data.
On the report tab “3 separate queries” I have shown the fields that I would ideally like to be able to see in one table. Essentially I would like to SUM the costs from Consignment Costs Table use the WO v Cons No. link in Works Order Detail table to link it to the Sales Order data in USA Data Table. Hoping that makes more sense… :slight_smile:
Lizzie

AMCNA Gross Margin Test.pbix (611.1 KB)

Hi @LizzieTompsett

In order to carry out those calculations, you need to activate bidirectional filtering between the WO_Detail table and the Consigment_details table.
This way the work orders header filter will propagate to the costs table

You can do this by using CROSSFILTER function or activating bidirectional filtering in your model. For example to activate the filter you can write your measures as :

Total Cost Bidirectional =
CALCULATE (
   [Total Cost],
    CROSSFILTER (
        WO_Detail[WHD_CONSIGN_NO],
        Consignment_Details[Consignment No.],
        Both
    )
)

In any case, I would recommend you develop your model further to make your measures simpler.

Best,

Diego

AMCNA Gross Margin Test 2.pbix (615.6 KB)

Hello Diego
This works perfectly, thank you so much for helping me, I am OK to add in all the normal measures now I have this connecting measure.
This post can be marked as solved!
Lizzie

1 Like

@LizzieTompsett my pleasure, I am glad to help.

Best,
Diego