Forum Discussion
Conditional Measure for specific row across two tables
Dear all,
I am looking to include a measure in my report which calculates the difference between inventory and open orders. The values come from different source tables which are connected by material as common value. The outcome of the measure should show the inventory balance for the specific material that is on the order showing in the corresponding row of the report.
My report looks like this:
In a new column, I want to add the measure showing inventory balance, so for the first row with order 1, it should show 10 as inventory balance (100 inventory minus open orders of 10 , 10 , 20 and 50). All measures with substraction that I tried give me results that are not corresponding to the single material on the row. Which measure should I use for inventory balance to reflect the correct value for this material specifically on the correct row in the report?
Thanks in advance for your help.
Below the source tables that I use, which have a relationship for Material_Plant.
| Order Number | Material_Plant | Net_weight |
| 1 | 001_X | 10 |
| 2 | 002_X | 20 |
| 3 | 003_X | 10 |
| 4 | 001_X | 10 |
| 5 | 001_X | 20 |
| 6 | 001_X | 50 |
| 7 | 003_X | 10 |
| 8 | 001_Y | 20 |
| 9 | 001_Y | 10 |
| 10 | 002_Y | 20 |
| 11 | 004_Y | 10 |
| 12 | 004_Y | 50 |
| 13 | 005_Y | 30 |
| Material_Plant | Inventory |
| 001_X | 100 |
| 002_X | 100 |
| 004_Y | 100 |
| 005_X | 100 |
| 005_Y | 100 |
7 Replies
see attached
Thank you so much for this! An added complexity in my data set (I simplified it for the purpose of the post) is that every order also has a loading date, and orders with a higher order number could have an earlier loading date than an order with a lower order number. Is there a way to reflect loading date in your code, so that an order that loads earlier is consuming inventory first? I appreciate the help.
Hi,
So in that new column (result of a measure), you want to show 10 in each row where 001_X is found? Am i correct?
Since there is an additional complexity (as mentioned by you in another post), share a representative dataset and show the expected result.
