Visualizing warehouse and product structure

[Niko]

Hi everyone!

I'm reasonably new for powerBI and I've been thinking if I could model our companys warehouse more convenient way with powerBI. I've attached couple pictures how does it look when pull the data from database.

There is two tables in one of them there is the quantity how many of the products and components there are in warehouse.

The other table tells the product structure. For example part 1775001 I need components 1775002..003...004. Product 1775005 needs components 006..007..004. There is also the amount of the components that are needed to assembly the product. Could you help me to give an idea how to get forward with this thing. How to merge these two tables? I tried to do it but didn't get the result that I wanted.

My Idea would be that there would be a bar chart where I could click the product and then I could drill down to component level and see the component supply levels.

Cheers Niko

[fhill · Accepted solution]

For me, the confusion is how you have your assembled parts and component parts in the same table for QoH.  This isn't impossible to work with, but I had to create some fake names below (A, A01, A02) to help me seperate the data for visuals.

Screen Shot 1 - Your two raw tables:

Screen shot 2:  You have to define a Relationship very specificically to tell Power BI how to account for the duplcication of data between the two tables.  Part has to link to Com_Part with a 1:Many relationship!  **** This is the key to all other data analysis. ****

Screen Shot 3:  Now you can create a table with Part and Com_Part and Qoh from the 2 different tables and Power BI knows how the 'Relate' to each other:

   

Screen Shot 4:  With the Quantity Part Assembly value changing sub-part by sub-part my first thought of how to use the data was to look for parts with possibly a HIGH QoH, but because you need X of them to make a Bigger Part, there might be a actionable value to looking at the number of Bigger Parts you "could assemble" with your QOH of Sub-Parts.  

Potential Assembly = SUM(QOH[QoH]) / SUM(Part_Breakdown[Qty_P_Ass])

Screen Shot 5:  Finally trying to look at the bar graphs you requested.  You can 'stack' you Axis now with Part and Com_Part to get drill thru, but I wasnt' able to make it look like I think you had intended.  Play with your values from here and see if you can get anything actionable out of your real data!

Hope this helps,

FOrrest