Sales Volume of components when combined parts can be used to make lots of other combinations

[sg123]

Hi, 

First of all, a thank you.  I have been using Power BI for about a year and this forum has been a brilliant resource.

I'm trying to sum up components used in sales.  I have a table with all sales and each sale can be an individual component or a product made up of several components.  I want to be able to create a table which shows how many individual components have been sold in a time period.  The problem is that several components can be used to make a product and then that product can used to make another product, with some other components added and again it could be used to make another product again.  This could happen any number of times! 

As well as a sales table, I also have a table which shows the amount of each component used in each product that was made.  This instruction is different for each individual product made.    

I hope this makes sense!  I have posted an example below of the tables and what I am trying to achieve.  

If I was doing this in another program, I would probably use a loop and keep looping round until I got to the lowest component level for each product, but I can't work out how to do the equivilent in Power BI.  Perhaps there is a clever way of using calculated measures or something to achieve it instead?

Thanks so much 

Sales Table
Code	Component?	Product Combination?	Volume	Instruction Reference
1	Y	N	100
2	Y	N	200
3	N	Y	300	Ref 1
4	N	Y	1000	Ref 4
Instruction Reference Table
Instruction Reference	Component or Combination Number	Volume	Code
Ref 1	A	50%	3
Ref 1	B	25%	3
Ref 1	C	25%	3
Ref 2	A	50%	5
Ref 2	B	20%	5
Ref 2	3	30%	5
Ref 3	3	50%	6
Ref 3	5	50%	6
Ref 4	A	20%	4
Ref 4	6	80%	4
Component Sales (I need to create something like this)
Code	Individual Component/Code	Volume
1	1	100
2	2	200
3	A	150
3	B	75
3	C	75
4	A	660
4	B	210
4	C	130

Many thanks

[v-xulin-mstf]

Hi @sg123,

You can merge the two table.

And then try this in Power Query:

let
    Source = Table.NestedJoin(#"Sales Table", {"Instruction Reference"}, #"Instruction Reference Table", {"Instruction Reference"}, "Instruction Reference Table", JoinKind.LeftOuter),
    #"Expanded Instruction Reference Table" = Table.ExpandTableColumn(Source, "Instruction Reference Table", {"Component or Combination Number", "Volume"}, {"Component or Combination Number", "Volume.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Instruction Reference Table", "Individual Component/Code", each if [Instruction Reference]=null or [Instruction Reference]="" then [Code] else [Component or Combination Number]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "nVolumn", each if [Instruction Reference]=null or [Instruction Reference]="" then [Volume] else [Volume]*[Volume.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Component?", "Product Combination?", "Volume", "Instruction Reference", "Component or Combination Number", "Volume.1"})
in
    #"Removed Columns"

Here is thie output:

Please try the attached pbix.

Best Regards,
Link

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

[sg123]

Thank you so much for your help and suggestion.  It works fine for the 3 out of the 4 scenarios in the example.  I've adapted it for the 4th example, but it's still very manual and I think it'll be quite resource heavy when it uses real life data. 

Anyway, to try and explain what I've done:

I merged the Instruction Reference Table on itself 3 times to get it to look up the lowest level of component/combination number.  Once I have this, I can multipy the percentages together with the sales volume to get the volume of each component (an extended version of what you did in Merge1).

My problem is that I might have many more than 3 layers and I don't want to hardcode 20 joins.  For a start, I might need 21 or I might only need 4 and so the remaining 16 joins are unnecessary processing. 

I hope that makes sense and thank you so much again for your help with this so far.

I've tried to upload my working file to Google Drive, so hopefully the following link will work.  https://drive.google.com/file/d/1ewfPW7s7lUaWUPiZoqWaSEssf3qSJaG_/view?usp=sharing

[sg123]

Just to add, I think I've put it on Google Drive ok.   Hopefully it'll let you download it!

[v-xulin-mstf]

Hi @sg123,

If I understand correctly, you have multiple Instruction Reference Tables.
In that case you need to append all Instruction Reference Table tables.
Then merge to the Sales Table table.(Merge1)

If you have multiple Sales Table tables, you also need to append the Sales Table tables first.

If the issue is still unresolved, please reply to me.

Best Regards,
Link

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

[sg123]

Hi,

Thanks again for your reply. 

No I don't have multiple Instruction Reference tables, I just made a copy of it to use in the project as I wanted to change the type of one of the fields. 

What I have is that when I make a combination of componets, that combination can be sold or it can be used as part of a second combination.  Combinations of components can be used in further combinations any number of times.  

So to try to explain;  I make a combination called Pack 1.  It is made using some of each of component A, B and C. 

Pack 1 can be sold as it is or used as an ingredient to make pack 2. 

So pack 2 could be made up of componant A and B, but also with some Pack 1 as well. 

This possibly could go on and on, so for example, Pack 50 could be made up of some of Pack 1, some of Pack 10, some of Pack 40 and maybe component B and C too. 

So  I need to merge the instructions table on itself depending on how many times a combination pack is used in the recipe for another combination pack.  The problem is I don't know how many times I need to merge it upon itself.  For example, it's possible that Pack 50 could be made up of a small bit of each of all of the packs 1-49, so I would have to merge the instructions table on itself 49 times!  I was wondering if there is an effectent way to loop the merge so that I only merge the number of times necessary, or another way to do it as I think merging multiple times will not be very efficient?

Sorry I'm probably not explaining it very clearly!  Thanks again

[v-xulin-mstf]

Hi @sg123,

The manual merge you require is possible, I don't think the dynamic loop merge is supported.

Best Regards,
Link

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