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