Connecting two tables with allocation

[aeldiasty]

Hi -

I am a new user of PBI. I have two tables, table #1 has connection with multiple items in table #2 which has time series values. Table #1 has % allocation for each line, which means for example line 1 in table #1 is linked to line #4 and line #7 in table #2 but needs to multiple all values of line #4 by 60% and line #7 by 35%.

I already have a column with this allocation value in table #1, but I am wondering how can I do this connection between the 2 tables with the allocation column in action?

Your guidance is highly appreciated.

Thank you,

AE

[amitchandak]

@aeldiasty ,  Need sample data to say that.

[vivran22]

Hello @aeldiasty 

Is it possible to share the sample data structre for the tables and the expected output (do you need it as a measure or a column)?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[aeldiasty]

It deosn't matter if it is column or measure. My data is something similar to the below, I undertand I will ahve to unpivot the Table #2, but I am stuck at replicating somethin like vlookup then multiply each of Sub-iitem value by the corresponding allocation in the connected Item.

Table 1				Table 2
Item	Allocation	Sub-Item		Sub-Item	1/1/2021	2/1/2021	3/1/2021	4/1/2021
1	30%	A		A	$100	$90	$120	$80
1	23%	B		B	$230	$200	$190	$220
2	77%	B
3	70%	A

Thank you,

AE

[amitchandak]

@aeldiasty , share the final output. Mark contributes @

[aeldiasty]

@amitchandak Please see below the final goal results. Basically, Table 1 with looking up Sub-item in Table 2 then get the cash value, then multiply by the allocation value.

Table 3
Item	Allocation	Sub-Item	Month	Cash
1	30%	A	1/1/2021	$30
1	30%	A	2/1/2021	$27
1	30%	A	3/1/2021	$36
1	30%	A	4/1/2021	$24
1	23%	B	1/1/2021	$53
1	23%	B	2/1/2021	$46
1	23%	B	3/1/2021	$44
1	23%	B	4/1/2021	$51
2	77%	B	1/1/2021	$177
2	77%	B	2/1/2021	$154
2	77%	B	3/1/2021	$146
2	77%	B	4/1/2021	$169
3	70%	A	1/1/2021	$70
3	70%	A	2/1/2021	$63
3	70%	A	3/1/2021	$84
3	70%	A	4/1/2021	$56

[amitchandak]

@aeldiasty , Both in data transformation

1. Unpivot second table

https://radacad.com/pivot-and-unpivot-with-power-bi

2. Merge Table1 and Table 2

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[vivran22]

@aeldiasty 

It is recommended to use Power Query for such transformations and calcualtions instead of using DAX, as it is built for that.

Let me know if this solves the purpose.

Solution file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn