Trying to split a value into multiple rows based on a table of categories with percentages.

Hi,

I have currently have 2 tables:

• Sales - This is a large data table which contains sales information.
• Percentage Table - This table has 8 different categories (these are segments) which a customer may be allocated across.

I am trying to use the percentage table allocate a portion of the sales figure to each of the categories. Im not sure on the best way to achieve what im looking for, appreciate any help which can be given.

Example:

Sales Table 

Customer | Amount £ | Year       | Period

1234         | £100         |2022/23 | 10

Percentage Table 

Customer | Cat 1 | Cat 2 | Cat 3 | Cat 4 | Cat 5 | Cat 6 | Cat 7 | Cat 8

1234         |70      |  0       | 0      | 0        |10       |10      | 0       |10

Expected Outcome

Customer | Amount £ | Year       | Period  | Category

1234         | £70           |2022/23 | 10         |  Cat 1

1234         | £10           |2022/23 | 10         | Cat 5

1234         | £10           |2022/23 | 10         | Cat 7

1234         | £10           |2022/23 | 10         | Cat 8