How to allocate a demand from one table to another (manufacturing plant to manufacturing lines)

[o59393]

Hi all

I need to allocate the demand in several production plants into manufacturing lines.

The concept is the following with an example:

I have selected the plant "Los Volcanes" and product "Ref glass colas 500", it has the breakdown of the demand by month.

My intention is to allocate that demand but by the manufacturing lines of Los volcanes:

In the above image you see for the same plant and product filtered, the manufacturing lines for that plant.

In yellow is the expected result I'd like to achieve, pretty much is a multiplicaition of the demand by plant of the first image by the % produced in that line of the second image.

So in the second image, for example for Jan 2020, line 2 (produces 20% of demand) and line 3 (produces 80%) should give us 75,547.10

I have tried it on power bi but I get many repeated values:

In (1) you see the dax I used to calculate the demand by plant that is correct and in (2) you see the demand by line which is wrong.

In the excel attached, sheet "Allocation of demand by line" column F you will see the formula used. Pretty much a Vlookup of concatenations to get the result desired.

Pbix & excel: https://1drv.ms/u/s!ApgeWwGTKtFdhwAcrx9gEKuQkiHZ?e=xjX2Ca

Note:

Demand by plant sheet is the equivalent to the left table of the power bi.

Allocation of demand by line is the equivalent to the right table of the power bi.

Any help is very appreciated.

Thanks community.

[o59393]

Hi all

I need to allocate the demand in several production plants into manufacturing lines.

The concept is the following with an example:

I have selected the plant "Los Volcanes" and product "Ref glass colas 500", it has the breakdown of the demand by month.

My intention is to allocate that demand but by the manufacturing lines of Los volcanes:

In the above image you see for the same plant and product filtered, the manufacturing lines for that plant.

In yellow is the expected result I'd like to achieve, pretty much is a multiplicaition of the demand by plant of the first image by the % produced in that line of the second image.

So in the second image, for example for Jan 2020, line 2 (produces 20% of demand) and line 3 (produces 80%) should give us 75,547.10

I have tried it on power bi but I get many repeated values:

In (1) you see the dax I used to calculate the demand by plant that is correct and in (2) you see the demand by line which is wrong.

In the excel attached, sheet "Allocation of demand by line" column F you will see the formula used. Pretty much a Vlookup of concatenations to get the result desired.

Pbix & excel: https://1drv.ms/u/s!ApgeWwGTKtFdhwAcrx9gEKuQkiHZ?e=xjX2Ca

Note:

Demand by plant sheet is the equivalent to the left table of the power bi.

Allocation of demand by line is the equivalent to the right table of the power bi.

Any help is very appreciated.

Thanks community.

[V-lianl-msft]

Hi @o59393 ,

I checked your sample file and found that the issue is caused by the model. You can try merging "query1" with "plant to territory" and create a relationship with "allocation.". Or add a "key" column for "allocation" and create a relationship with the master table.

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

[o59393]

Hi @V-lianl-msft 

I tried with both solutions you suggested.

I merged the tables "plant to territory" and "query1" , then with that table I created a concatenation of the Plant & Product Normalized & Month

Then, for the table called "Allocation" I created a concatenation of Plant & Product Normalized & Month

I linked both tables and got a many to many relationship:

And the measure "% of demand by line" is now showing correct values:

Even though it works, from what I heard Many to many is not a good idea right? In this particular case there might be no other choice since it can't be one to many. For example a product normalized can have many different BPP codes (column name) of merge1 table.

Let's say if I filtered from the table "Merge1" product normalized "NR PET Colas 2000" in plant "Guatemala City", shipped from "UO CHIMALTENANGO" and for month "Jan 2020" we would get 5 rows. Column BPP and L1.6 Type Bev is what causes the 5 rows mentioned.

The other choice, which is the key column for "allocation" and "master table", I tried concatenating "Bottler" & "Product normalized" from both tables but still didn’t work. If I included the month in the concatenation for the master table would give an error. 

Which fields should I concatenate do make it work in this second option?

I attach the most recent Pbix with the solution of merged queries.

https://1drv.ms/u/s!ApgeWwGTKtFdhwLMvbTvNQCmMl3H?e=kBXnFL

Let me know your thoughts.

Thanks a lot @V-lianl-msft 

[amitchandak]

@o59393 , You need to have a table with line and percent.

Line percent

2     .2

3     .8

then do a cross join in a measure just multiple with precent

 new table = summarize(crossjoin(Table1,Allocation),[plant],[month],[line],[product normalized], "expected", sum([demand by plant])*max([percent ]))

[o59393]

Hi @amitchandak 

Thanks for the help. Why would I need a table of line and percent? and with which other tables should I join them?

Thanks.