OK, thanks.
First did you consider using the grouping feature in your View? In your view settings you can group by the “Sub Category” and “Area” columns, and you’ll be able to automatically display a count or a sum for your number columns.
You can additionally add grouping also by your “On hand” column, so for example, your first column to group by will be “On hand”, also sort by this column in descending order so that you first see the records with "1"s, and the view will show you the count/sum. The second column to group by for example would be “Area”, so that when you expand your first group, you’ll see the count/sums per each area code, then comes the third columns to group with “Sub Category”, so that when you expand each area you’ll see the sub categories inside.
If you are interested in seeing only the records where “On hand” column has a value of “1”, you can create a slice for this then create a view based on this slice not the table. The row filter condition for this slice would be:
[Location_Total_On_Hand] = 1
Then you group this view similarly by area and subcategory. So far, doing this does not require any expression, just tweaking of your views.
Would that answer your need?
