Forum Discussion
How can I create this table or matrix ?
Hi community,
I have two tables just like the next examples;
Table 1
| Item | Total Qty to Deliver | OnSite Available Pcs | Offsite Available Pcs | Remain | Total Available Pcs |
| A | 320 | 192 | 288 | -128 | 480 |
| B | 225 | 100 | 234 | -125 | 334 |
| C | 245 | 37 | 214 | -208 | 251 |
| D | 12 | 35 | 264 | 23 | 299 |
| E | 0 | 29 | 272 | 29 | 301 |
Table 2
| Item | Pallet | Boxes | Pices |
| A | 00000000000100079197 | 28 | 168 |
| B | 00000000000100179255 | 17 | 102 |
| C | 00000000000100230567 | 24 | 112 |
| D | 00000000000100021493 | 22 | 132 |
| E | 00000000000100126754 | 22 | 132 |
| A | 00000000000100291209 | 22 | 120 |
| B | 00000000000100299335 | 22 | 132 |
| C | 00000000000100183603 | 17 | 102 |
| D | 00000000000100030921 | 22 | 132 |
| E | 00000000000100079197 | 22 | 140 |
The Table 2 is the OffSite Available Stock, but it's splited by Pallet.
I need a table/matrix that gives me the pallets to bring for can supply the qty to deliver, trying to bring the most matchable pallet by qty between the remain that i need to complete & the qty in the pallet.
Something like this;
| Item | Pallets To Bring | Pallets To Bring ID | PCS |
| A | 1 | 00000000000100079197 | 168 |
| B | 1 | 00000000000100299335 | 132 |
| C | 1 | 00000000000100183603 | 102 |
| C | 1 | 00000000000100230567 | 112 |
| D | 0 | Null | Null |
| E | 0 | Null | Null |
How can i make it possible on Power BI?
I've trying for days and I still have no idea about to create this table...
I hope you can help me,
Thank you.
2 Replies
Hi Anonymous ,
Seems like your expected output not very clear. For example, in your final table, why Item C should have two rows, how to define its calculation logic because I cannot see other relationship between two tables except items. Also, if you want Item D,E show null, maybe remain>0? But how to define other items...not certain
Perhaps need more details about this issue for further discussion.
Best Regards,
Yingjie Li
Hi v-yingjl ,
Thanks to take time to try to help me, ill try to be more clear...
For example, in the case of item B, the request (qty to deliver) is for 225 pcs, On Site I only have 100, the output table or matrix gives me pallet 00000000000100299335 with 135 pcs cause I need 125 pcs more to complete the request.
In the case of C, (thats why I think it could be a matrix), the request (qty to deliver) is for 245 pcs, On site I only have 37 pcs, I need to bring 208 pcs more from the stock out of the site (table B).
In the table B, I have two pallets of item C; 00000000000100230567 with 112, and the 00000000000100183603 with 102, 214 pcs in total. Reason why the output table or matrix gives me the two rows for item C = the two pallets that i need to complete the request.
In the case of D & E items, I dont care if i have or not rows of them in the output table, because i can supply the request with the Onsite pcs or dont have request. I think is more logical to dont have rows of them if i dont need to bring pcs.
I hope I have been clearer, let me know if i need to explain something more or in a diferent way. Thank you.
