Forum Discussion
Connecting two tables with allocation
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 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
