Boom Bill of material

[pakovic]

Hello, good morning. Thank you in advance. I wanted to ask if you could help me. I have a products table and a materials list:

Products Table:

No	Descripción	Categoría	Precio
1	Red wine rack	Finished product
2	Glass bottle	Auxiliary	2.50
3	Label	Auxiliary	1.50
4	Back label	Auxiliary	1.20
5	Cap	Finished product
6	Capsule	Auxiliary	0.90
7	White wine rack	Finished product
8	Cork	Auxiliary	1.00
9	Pink wine rack	Finished product
10	Summer wine box	Finished product	5.00

Materials Table:

parent_no	no	descripcion	cantidad por unidad
5	6	Capsule	1
5	8	Cork	1
1	2	Glass bottle	1
1	3	Label	1
1	4	Back label	1
1	5	Cap	1
9	2	Glass bottle	1
9	3	Label	1
9	4	Back label	1
9	5	Cap	1
10	1	Red wine rack	1
10	7	Pink wine rack	1
10	3	Label	1
7	2	Glass bottle	1
7	3	Label	1
7	4	Back label	1
7	5	Cap	1

I would like to be able to filter by parent number in a Power BI report and have the corresponding materials list, quantity, and cost price appear. For example, if I filter by product 10 "Summer wine box," which contains 2 finished products and a label, I would like to see the following information in the Power BI list:

No	Descripción	cantidad	precio
2	Glass bottle	2	5
3	Label	3	3
4	Back label	2	2,4
6	Capsule	2	1,8
8	Cork	2	2
			14,2

And if I select the finished product 1 "Red wine rack," I would like to see the materials list:

No	Descripción	cantidad	precio
2	Glass bottle	1	2,5
3	Label	1	1,5
4	Back label	1	1,2
6	Capsule	1	0,9
8	Cork	1	1
			7,1

This is the pibx

https://www.dropbox.com/s/x9j1erp24jvylas/boom%20material.pbix?dl=0

[joaoribeiro]

@pakovic ,

Do these materials should appear for all finished product? If not, you must create another table to map finished products to their specific auxiliary materials.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[joaoribeiro]

Hi @pakovic 

The PBIX file seems to be working fine. The reason you are not seeing values for some categories, is because there are no prices for some of them. But the relationship seems correct to me. Try to insert more data into the tables to check if the results will be the expected.

If you need any additional help please @ me in your reply.

Hope this answer solves your problem!
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro

[pakovic]

but I want when I select product 10 "Summer wine box  I want to show this products.

No	Descripción	cantidad	precio
2	Glass bottle	2	5
3	Label	3	3
4	Back label	2	2,4
6	Capsule	2	1,8
8	Cork	2	2

[pakovic]

red wine rank +  pink wine rank