cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Prodigy

Hello everybody!

I am just struggling with my data model.

Actually I am working within Excel/ Power Pivot but I am sure you can bring my new insight although.

I have three tables:

1. BOM: Lists the Parent and Child Item. The can be more than 1 child per parent.

 Parent Child A A1 A A2 A A3 B B1

2. Sales Table: Sales per Child

 Article (child) Sales Date A1 5000 01.12.2019 A2 500 01.12.2019 A2 500 02.12.2019 A3 500 01.12.2019

3. Item Master:

 Article (Parent and Child) Type Size Area Department A Metal 550 Manufacturing Mounting A1 Metal 500 Manufacturing Mounting

How would you combine/ link that tables?

In the end, I need the Count of Sales per Article on parent level multiplied by the size of the parent.

For example:

01.12.2019 5000 sales on article A1 = (5000/500 (Size of article A1) ) * 550 = 5500

How are you building data models like that?

Would you extent the item master with the parent part?

Would you split the item master into Item Master Parent and Item Master Child?

Best Regards

1 ACCEPTED SOLUTION
Community Support

You may use LOOKUPVALUE to add calculated columns.

``````Column =
LOOKUPVALUE (
'Item Master'[Size],
'Item Master'[Article (Parent and Child)], 'Sales Table'[Article (child)]
)
``````
``````Column 2 =
LOOKUPVALUE (
'Item Master'[Size],
'Item Master'[Article (Parent and Child)], LOOKUPVALUE ( BOM[Parent], BOM[Child], 'Sales Table'[Article (child)] )
)
``````

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support

You may use LOOKUPVALUE to add calculated columns.

``````Column =
LOOKUPVALUE (
'Item Master'[Size],
'Item Master'[Article (Parent and Child)], 'Sales Table'[Article (child)]
)
``````
``````Column 2 =
LOOKUPVALUE (
'Item Master'[Size],
'Item Master'[Article (Parent and Child)], LOOKUPVALUE ( BOM[Parent], BOM[Child], 'Sales Table'[Article (child)] )
)
``````

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Champion

You can use Power Query and Merge Item Master into BO table twice, once on Parent and then on the Child, later expand the columns and prefix the names with Child and Parrent.

Now you can load the Mreaged table ( in my example called Article ) and create a relationship with Sales on Child, later you can use below measure.

``Measure = SUMX( Sales, ( Sales[Sales] / RELATED( Article[Child Size] ) ) * RELATED( Article[Parent Size] ) )``

M code for article ( you can paste it into Blank Query )

``````let
Source = BOM,
#"Merged Queries" = Table.NestedJoin(Source, {"Parent"}, #"Item Master", {"Article (Parent and Child)"}, "Parent ", JoinKind.LeftOuter),
#"Expanded Parent " = Table.ExpandTableColumn(#"Merged Queries", "Parent ", {"Type", "Size", "Area", "Department"}, {"Parent Type", "Parent Size", "Parent Area", "Parent Department"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Parent ", {"Child"}, #"Item Master", {"Article (Parent and Child)"}, "Item Master", JoinKind.LeftOuter),
#"Renamed Columns" = Table.RenameColumns(#"Merged Queries1",{{"Item Master", "Child."}}),
#"Expanded Child." = Table.ExpandTableColumn(#"Renamed Columns", "Child.", {"Type", "Size", "Area", "Department"}, {"Child Type", "Child Size", "Child Area", "Child Department"})
in
#"Expanded Child."``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Old fashion principle is never mix data in a column. You have one column that contains parent or child. I think that means it has to be split into 2 tables of Parent Item Master and Child Item Master and then its easier but obviously will have some more complex related info as the child values I assume will override the parent values.

L

Post Prodigy

How would you join them together? I mean, where is the key between the child and parent item master? Would you add a column into the child item master with the corresponding parent and link that column with the parent item master?

What kind of information do you need?