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

[MatthewA1]

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

[DataInsights]

@MatthewA1,

Try this Power Query solution. I added a second customer for additional testing.

Unpivot Category columns in Percentage Table and exclude rows with 0:

Create a new table by merging these two tables (join on Customer). In the new table, create a custom column:

[Amount] * ([Value]/100)