Multiple column calculation based on secondary input file

[je2]

I'm not sure if the title accurately conveys what I want to do, but I have two source files and I need to do a calculation to split out sales by market for each product category. I don't know if I'm going about this the right way, so I was hoping someone could point me in the right direction. 

My main data source looks like the following:

Customer	Product Category	Sales	Year
A	001	1,000	2019

From this, I have split out the product category by various percentages based on the market the product is sold into. My secondary data source:

Product Category	Mkt 1	Mkt 2	Mkt 3	Mkt 4
001	25%	25%	10%	40%

I want to calculate the sales based on the percentage splits and have it in the following format so I can pull this into Tableau. 

Customer	Product Category	Market	Sales	Year
A	001	Mkt 1	250	2019
A	001	Mkt 2	250	2019
A	001	Mkt 3	100	2019
A	001	Mkt 4	400	2019

I'm thinking I could possibly add additional columns and do a calculation for each market type and then transpose the new individual market columns with their split sales into one 'Market' column as shown in the last table, but I'm having trouble. Is this the best method for this?

Any help would be appreciated. Thanks! 

[Jonathan-Sherman · Accepted solution]

Hi @je2,

I think this workflow solves what you're looking to try and achieve? I'm pivoting your second data source, joining both data sources together and calculating the percentage of sales each record makes up.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[je2]

Thanks, @Jonathan-Sherman! This is exactly what I was trying to do, but wasn't sure how to go about it. 

Best,

je2