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 mat...
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?
- Hi Ashish, Yes that is correct, the outcome would need to be shown in every row.
Since there is an additional complexity (as mentioned by you in another post), share a representative dataset and show the expected result.
Adding the loading date, the order table would look like this:
Order Number Loading Date Material_Plant Net_weight 1 25/12/2023 001_X 10 2 27/12/2023 002_X 20 3 27/12/2023 003_X 10 4 05/12/2023 001_X 10 5 08/12/2023 001_X 20 6 08/12/2023 001_X 50 7 16/12/2023 003_X 10 8 31/12/2023 001_Y 20 9 06/12/2023 001_Y 10 10 08/12/2023 002_Y 20 11 25/12/2023 004_Y 10 12 26/12/2023 004_Y 50 13 03/12/2023 005_Y 30 And the report would look like this:
I would be happy with having the calculated remaining inventory measure to reflect the inventory minus all summarized order weight on each row, like this:
Order Number Loading Date Material_Plant Net_weight Proj Inventory 1 1 25/12/2023 001_X 10 10 2 27/12/2023 002_X 20 80 3 27/12/2023 003_X 10 -20 4 05/12/2023 001_X 10 10 5 08/12/2023 001_X 20 10 6 08/12/2023 001_X 50 10 7 16/12/2023 003_X 10 -20 8 31/12/2023 001_Y 20 -30 9 06/12/2023 001_Y 10 -30 10 08/12/2023 002_Y 20 -20 11 25/12/2023 004_Y 10 40 12 26/12/2023 004_Y 50 40 13 03/12/2023 005_Y 30 70 Or even better, in the way that lbendlin has proposed with a decreasing projected inventory, but then based on loading date sequence rather than order number sequence.
Order Number Loading Date Material_Plant Net_weight Proj Inventory 2 1 25/12/2023 001_X 10 10 2 27/12/2023 002_X 20 80 3 27/12/2023 003_X 10 -20 4 05/12/2023 001_X 10 90 5 08/12/2023 001_X 20 70 6 08/12/2023 001_X 50 20 7 16/12/2023 003_X 10 -10 8 31/12/2023 001_Y 20 -30 9 06/12/2023 001_Y 10 -10 10 08/12/2023 002_Y 20 -20 11 25/12/2023 004_Y 10 90 12 26/12/2023 004_Y 50 40 13 03/12/2023 005_Y 30 70 Thanks again for your help.