Complex problem in power BI, multiplying values in a table with certain values from another table.

[Anonymous]

Hello, 

I was tasked with a problem regarding Power BI. I now know only the basis of it, but I can already make reports and simple visuals. However, part of the report that I am currently trying to create involves something analogous to modifying certain values in the initial table. I am now wondering if there is a solution to this in the BI.

Here is a photo of the table, which contains values for 9 different buisness types and 4 regions, and it means that for every buisness that we have, a certain value is distributed over the four regions according to the percentages provided. For example, a net sale for the BU of AX would "give" 36% to region1, 13% to region2 etc. The distribution table

 Here is a relationships table which basically tells us uh ... well, the relationships. You can see that the table above is related to the main table "Sales" through the "Buisness Segments" table.

Relationships

So, the problem now is that some of the sales contribute their values to a certain region, which is not indeed true. In order to fix that, I should distribute it's value over the four regions.

For now, I think the problem looks like this: 

1. Firstly, I need to find those sales. I know how to do this, they all have a certain ID.
2. I then have to group all of those sales by buisness type so that they would have a direct (maybe 1:1 if possible? not sure) connection to the buisness segments table. I might need to include an intermediate table so that only certain values get affected.
3. I then would have an array of values [w1, w2, w3, w4], the percentages from the table above that correspond to a certain sale from the sales table. I would then be able to create four new objects net_sale*w{1,2,3,4} that would give me the distibution of net sales over the regions 1-4.

Thus, my questions are:

1. How do I group values in a table? Can I group them by a parameter from another table that yet has a relationship to it? (i.e group some of the values from the sales table by their buisness types from the buisness segments table)
2. How exactly could I get an array of values in BI? 
3. Is it possible to multiply a value from one table by value from another table?

I hope that someone reads through this problem, I understand that there is a lot of info, but I really tried to pose the question as clean as I could. Please ask me if there is some other info that is needed. Unfortunately, I could not post the BI file due to security.

Thanks, 

Anton

[CNENFRNL]

First of all, unpivot your Cost Collector table to turn it into a one-dimensional table for further analysis.