Forum Discussion
Connecting two tables with allocation
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 exam...
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
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
aeldiasty , share the final output. Mark contributes @
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
