Forum Discussion
Multiple line Qty Subtraction from Single Data
Hi ,
Hope this would be simple doubt. But I need help to crack the solution in Power bi
Have Two tables:
Table 1: Same part # stock received on muliple dates / Unique Ref.
Table 2: Usage of the parts without any reference
Output Needed :
1. Each line in Table 1 should have a new coulum with the Qty used, which should not be greater than the received qty
2. The Qty considereded for the previous line from table should be deducted for the next line calculation
| Table 1 | ||
| Unique Ref ID | Part # | Received Qty |
| ID1 | A | 2000 |
| ID2 | A | 2000 |
| ID3 | A | 2000 |
| ID4 | A | 2000 |
| ID5 | B | 2500 |
| Table 2 | |
| Part # | Qty Used |
| A | 7000 |
| B | 5000 |
| OutPut Expected | |||
| Unique Ref ID | Part # | Qty | Qty Used (Table 2) |
| ID1 | A | 2000 | 2000 |
| ID2 | A | 2000 | 2000 |
| ID3 | A | 2000 | 2000 |
| ID4 | A | 2000 | 1000 |
| ID5 | B | 2500 | 2500 |
Thanks
Mak
Try like new columns
Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty]) col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used]) col3 = if((col2-col1)<col2,(col2-col1),col2)Appreciate your Kudos.
4 Replies
Try like new columns
Col1 = sumx(filter(table1, table1[Part #]=earlier(table1[Part #]) && table1[Unique Ref ID]<=earlier(Unique Ref ID])),table[Received Qty]) col2 = sumx(filter(table2, table1[Part #]=table2[Part #]),table2[Qty Used]) col3 = if((col2-col1)<col2,(col2-col1),col2)Appreciate your Kudos.
Hi Amit,
It works. Thanks for the solution.
Regards,
Mak
First, can you guarantee that Unique Ref ID will always sort alphabetically such that the earliest "instances" are always alphabetically "less" than later "instances". If that cannot be guaranteed, do you have a date column or index column where you can distinguish "earlier" from "later"?
Because if you don't, this will be difficult if not impossible.
Hi Greg,
Yes , the unique ref ID are sequenced numeric data which can be sorted from earliest. In that case what solution can support in this case.
Thanks,
Magesh
