Creating a manufacturing production report

[HilaryCox]

I want to create a manufacturing production report where I can select two or more finished goods with a What if parameter for each selection.

Example:
production run 1 = 25 x Red Shoes
production run 2 = 45 x Black Shoes
production run 3 = 10 x Green Shoes

The bill of materials (BOMs) for these shoes has some unique components and some shared components. I am trying to create a report that allows the Production Team to select the finished goods they want to create and the number of units for each of these finished goods. The results would be a table that shows Available stock of all components, quantity of components needed for all 3 production runs and a calculated column showing the over/under for each component.

I want to be able to show the results in one table. So far, I have been able to create a table for each selected finished good with a What if parameter applied for all unique components and a separate table for all shared components. The problem I am running into is that the BOMs for each finished good are slightly different. For example, all shoes BOMs use laces as a component however to produce Red Shoes you need 100 yards of laces and the produce Green Shoes you need 150 yards of laces. So, if you want to produce both you need to multiply 100 x the number of Red Shoes and 150 x the number of Green Shoes then add those together. Since Red Shoes and Green Shoes are both finished goods, I can't get the table to show both finished goods and apply the correct multiplier for the components.

[Anonymous]

Hi  @HilaryCox ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HilaryCox]

Hi @Anonymous 

Thank you for the response. I will do my best to share some data to provide more context.

I have 3 different finished goods I want to make. Some have shared components and some have unique components. Most of the shared components have different quantities per component. I want to be able to create a report that can provide a table to answer the following question.

If I make 20 cases of Barrel Bourbon and 40 cases of Barrel Rye how much of each component do I need to have in stock?

Here is some data for reference.

Finished Good #1	Barrel Bourbon
Finished Good #2	Barrel Rye
Finished Good #3	Blended Whiskey

Finished Good	Item Description	UoM	Quantity
Barrel Bourbon	Aged Bourbon	GAL	1.74
Barrel Bourbon	Vanilla Extract	GAL	0.105
Barrel Bourbon	Vanilla Bean	Each	0.029
Barrel Bourbon	Water H2O	GAL	0.533
Barrel Bourbon	Whiskey Bottle	Each	12
Barrel Bourbon	Closure	Each	12
Barrel Bourbon	Label	Each	13
Barrel Rye	Aged Rye	GAL	1.379
Barrel Rye	Aged Bourbon	GAL	0.399
Barrel Rye	Vanilla Extract	GAL	0.071
Barrel Rye	Vanilla Bean	Each	0.02
Barrel Rye	Water H2O	GAL	1.379
Barrel Rye	Whiskey Bottle	Each	12
Barrel Rye	Closure	Each	12
Barrel Rye	Label	Each	13
Blended Whiskey	Aged Rye	GAL	1.35
Blended Whiskey	Aged Bourbon	GAL	0.38
Blended Whiskey	Water H2O	GAL	0.65
Blended Whiskey	Whiskey Bottle	Each	12
Blended Whiskey	Closure	Each	12
Blended Whiskey	Label	Each	13

I have been able to create a report that I can select a finished good and set a what if parameter for that product. By editing the interations I was able to have both production runs to appear on the same page. But when I try and combine each output into one table I can't get them to add up correctly. I have tried converting the production run figures into variables but couldn't get it to work. I also tried to figure out a way to make the finished good selection a variable that I could include in the total table and then filter by those selections but I couldn't figure that out either. Here is a look at my report so far.