This content has been marked as final. Show 2 replies
Hello everybody, this is my first post and I come to you with a smart question.
I'm searching for a solution to this problem: I have a set of data coming from stock balances. My app must show the amount of available quantities for each Item.
The simplest expression would be [Stock Qty] - [Ordered Qty] = [Available Qty] that will be coded as Sum ([Stock Qty] - [Ordered Qty]).
Now comes the tricky part. Since I don't want any negative value my expression should be:
if ((Sum ([Stock Qty] - [Ordered Qty]) < 0, 0, Sum ([Stock Qty] - [Ordered Qty])) and at the lower aggregation level it works fine, but when it comes to do it to a higher level it doesn't make the job done.
I have this scenario:
|Warehouse||Item||Stock Balance||Ordered Qty||Available Qty|
If I create a pivot table or a Bar Chart based on Warehouse field in Dimension and Available Qty field in measure, the result is the following:
But i would like to be:
Is there a way to tell the system to always sum at the lowest level to obtain the above values.
I hope I made myself clear.