Two problems.
First, if you use a simple + or - and a value is null, the result will be null. 5 + null = null. That's making a lot of your results null. In fact, it appears to me that even your TOP table is wrong for this reason. If I understand what you intended, it certainly doesn't appear that you wanted the first line to be 0. It appears that the first line should be:
30.446 Other_Parts.Qty On Hand
-45 -Other_Parts.Safety Stock Qty
-85.179 -Calc Qty
+null +Purchase Qty
-null -Total Received Qty
-------
-99.733 =Desired Total
The second line should be:
-99.733 previous line
-null -Calc Qty
+90 +Purchase Qty
-0 -Total Received Qty
-------
-9.733 =Desired Total
The third line should be:
-9.733 previous line
-0.829 -Calc Qty
+null +Purchase Qty
-null -Total Received Qty
-------
-10.562 =Desired Total
Right?
So the problem is that those nulls are making at least the first line start everything off wrong. You get away with it on the other lines because if you do sum(A), and A is null, the sum is 0, and some of those values above are written as sums instead of raw values. So using sum() is one way to force nulls to be 0, and rangesum() is another common solution. I'll just stick with sum() for now, which means putting every field in its own sum:
if(rowno()=1
,sum([Other_Parts.Qty On Hand])
-sum([Other_Parts.Safety Stock Qty])
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty])
// else
,above(column(3))
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty]))
And that produces the results it looks like you intended, at least in the first chart.
The second chart demonstrates your second problem, which is the one you noted, and the one Karl gave the solution for. Expanding on what he said, functions like rowno() and above() work within a column SEGMENT, not a column. A column segment is that portion of a column that has every dimension value the same but the last dimension. It's probably easiest to see this with an example:
D1 D2 rowno() sum(X) above(column(2))
A X 1 1 null
A Y 2 2 1
A Z 3 3 2
B X 1 4 null
B Y 2 5 4
B Z 3 6 5
With your data on the other hand, rowno() is always 1, and above(anything) will therefore always be null.
Fortunately, that's just the DEFAULT behavior of those functions, and you can override it. Specifically, you can use the TOTAL keyword, and then if desired, specify the list of dimensions that you want to use to constrain what's in your column segment. The default behavior for the table above means that rowno() = rowno(total <D1>). In your case, you want the column segment to be the entire table, so you wouldn't put any dimensions in the list after "total". And above() works exactly the same way. So you need to modify the above expression a little further, to this:
if(rowno(total)=1
,sum([Other_Parts.Qty On Hand])
-sum([Other_Parts.Safety Stock Qty])
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty])
// else
,above(total column(3))
-sum([Calc Qty])
+sum([Purchase Qty])
-sum([Total Received Qty]))
That produces the results I think you want. Let me know if that's not really what you want.
.png "Luminary Alumni"){kind=icon}
