Product Structure and Power BI

[Snowy34]

Good day all,

I'm trying to create a visual to display product structure and some general information on that product. The information is pulled from one master stock on hand report, this report will hold all of the SKUs including the finished product as per the below screenshot. Please note that this report also has Wearhouse where this product is based.

The next screenshot is my product structure or BOM

Everything I tried so far has failed to make this work. What I would like to have is the following, 2 slicers, the first slicer would be the warehouse slicer as a drop-down for the user to select the WH, second would be the BOM drop down for the user to select what BOM they would like to look at.

The main visual would just display the components of the BOM selected and the Stock on Hand of them.

Please note some BOM's are not the same across all warehouse.

Any help with this would be much appreciated thank you.

Regards 

[v-lid-msft]

Hi @Snowy34 ,

Sorry for that, We cannot understand your data model clearly, Could you please provide a sample mockup table based on fake data or describle the fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.

Best regards,

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

[Snowy34]

Good day @v-lid-msft 

Attached is a sheet that you could use as TEST DATA 

Tab BOM is my BOMS structure, for example, BOM 6611 has 5 components that makeup 6611 items.

Tab SoH is my stock on hand report for all of my warehouses, this report holds the BOM code 6611 and all 5 components. 

Now I require two slicers, one of the slicers is the WH number as a drop-down that is simple. The second one is the BOM's, the user will select the BOM he will like to check stock on hand for. Something like the below screenshot.

Thank you again for helping out. 

[Ashish_Mathur]

Hi,

In the second table (SoH), I am confused with the content of the first column.  That column seems to contain data from the first 2 columns of the first table (BOM).

[Snowy34]

Good day @Ashish_Mathur 

The Stock On Hand report will have the finished code aka the BOM code and the components for that BOM.

The idea is the user can check to see his stock on the finished product aka the BOM and she/he can also check components to create a manufacturing order if required.

[Snowy34 · Accepted solution]

Good day all,

Just wanna give an update to this issue, I have managed to get it resolved, fixing up relationships and creating an index for the BOM data and playing with some of the filters 🙂 all in all good result its what I was looking for.

Once again thank you all for your input as it all did help 

Regards 

Snowy

[Ashish_Mathur]

Hi,

Using the Query Editor, ensure that you fill the blanks in the first column with Product Numbers.  Assuming the first column in your second image is the product, build a relationship from the first column of the first table to the first column of the second table.  To your visual, drag both columns from the second table and then write this measure

=SUM(Data[ONHANDBAL])

Hope this helps.

[Anonymous]

Create 2 respective slicers.

Build your BOM structure by creating hierarchy and as Rows/Values of matrix visual.

Format the matrix with option - "Stepped layout" for your desired output layout.

[Snowy34]

Hi sir,

Could you give me an example how to do that?

Should by file that holds my BOM be something like this

6611 | 116007/5 | 125111 | 145111 | 150511 | 151378

The idea is when I have the BOM codes as a slicers and I click on 6611 the visual would show the BOM codes and the stock on hand

[sturlaws]

Hi @Snowy34,

It is a bit unclear what you want to achieve, without knowing your data or model. Could you read through this post: How-to-Get-Your-Question-Answered-Quickly. It contains some good advice on how to best formulate your help request in this forum.

Cheers,
Sturla