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...
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