Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
joshua1990
Post Prodigy
Post Prodigy

Link or add BOM to Item Master and Sales Table

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.

ParentChild
AA1
AA2
AA3
BB1

2. Sales Table: Sales per Child

Article (child)SalesDate
A1500001.12.2019
A250001.12.2019
A250002.12.2019
A350001.12.2019

3. Item Master: 

Article (Parent and Child)TypeSizeAreaDepartment
AMetal550ManufacturingMounting 
A1Metal500ManufacturingMounting 

 

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
v-chuncz-msft
Community Support
Community Support

@joshua1990 

 

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.

View solution in original post

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@joshua1990 

 

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.
Mariusz
Community Champion
Community Champion

Hi @joshua1990 

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.

 

laura_gb
Advocate I
Advocate I

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

Thanks for your reply laura!

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.