Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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)] )
)
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)] )
)
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."
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?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |