In my inventory cube the stock values are stored at plant level and the stock quantities are stored at plant and storage location or at plant level. The column P1/Not assigned indicates that there is no storage location available for that record.
The data set will look like belowplant storage location Material stock value stock qty P1 P1/Not assigned Mat1 1257.34 0 P1 P1/Not assigned Mat2 7361.3 1 P1 P1/Not assigned Mat3 13.48 0 P1 P1/Not assigned Mat4 377.9 0 P1 P1/Not assigned Mat5 273.5 0 P1 P1/C01 Mat3 0 30 P1 P1/C01 Mat5 0 0 P1 P1/C03 Mat1 0 1 P1 P1/C03 Mat4 0 41 P1 P1/C07 Mat5 0 6
The expected output looking at webi level isPlant storage location Total stock value total stock qty P1 P1/not assigned 7361.3 1 P1 P1/C01 13.48 30 P1 P1/C03 1635.24 42 P1 P1/C07 273.5 6
To achieve above result I tried to calculate the unit price for each material then multiply with the quantity
The formula I used to calculate unit price is (stock value in (plant,material)/ stock qty in(plant,material))
Total stock value = stock qty * unit price , When used this formula on the block I am getting the total stock value as 9286.53 which is the total stock value for the entire plant. Where as I am trying to calculate the total value for each plant and storage location level.
Can anybody give some input on the calculation context can be used to get the above result.