How to get the sum /count of rows with a value but not show each row with that value...

[Tiger1]

I have an app that has a table - "Dispatch_report_query".   In this table, items are listed by id.  I have columns such as Location and category and sub category.  I need to count or sum the items that have a"1" by them if they are "On Hand".  I can get the sum/count - but when I put this in a view - it shows that total number per each ID:

I don't need it shown 3 times.  But it does that because there are 3 assets (ID rows) in the table.  I just want to show that count once.

This is the expression I currently have:

SUM(SELECT(Dispatch_report_query[Location_Total_On_Hand],
AND(
[_THIS].[Sub_Category_Code] = [Sub_Category_Code],
[_THIS].[Location_Code] = [Location_Code]
)
)
)

How can I do this?

[Joseph_Seddik]

You should have a separate table "Items", where you run this SELECT expression, instead of running it as many times as there would be rows in your current "Reports" table.

That "Items" table would list your unique items, one row for each item. 

Even better, if you establish a Ref in your Reports table to Items table, you won't even need to use SELECT. You will instead be using a simple expression like: COUNT([Related Reports])

[Tiger1]

Thank you, i will try this.

[Tiger1]

Its adding up the rows, But I need only the rows that have a "1" in the "On Hand" column:

So, now it does add up the rows - WITHOUT duplicating sub categories, however it is counting the row even if the item is NOT on hand.

[Tiger1]

I still cannot seem to make this work.  Here is a couple of snapshots of my original table:

 

 My company wants to be able to have a view in the app that shows the Table in a more condensed/total number view - without each row being shown.  So they want the total counts of each Item (Sub Category) PER location.  I cannot seem to get this to work.

[Joseph_Seddik]

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?

[Tiger1]

Yes and no.  Thank you for your thoughts and help.  I really do appreciate it.  In this picture you can see what they want.  This is a print of of the report they currently run from the software.   They want my app to do this for them:

 And again - here is the original table I have to use:

 

 Using groups, I what I need to do for Sub category only (see pic above).  But on the right hand side they want the total/counts PER sub category.   I can't seem to get this by simply using the one table they gave me.  It ends showing every item in the table (over 5,000 items) - with the total out to the side - NOT per sub category.

[Tiger1]

Any more thoughts?  See my reply below....  I still can't seem to make this work.

[Joseph_Seddik]

Thanks for the report photo. 

If you set your View as indicated, grouping first by Subcategory, then by Location, setting the Group aggregate as Sum of column "On hand", you'll get a view very similar to your report. 

Have you tried it already?  

If your aim is to have a printable report, then you can also have a similar result using templates. 

[Tiger1]

It almost works  - however.  See below for the difference.   

Here is what it looks like:

If I click "All" then it gives me this:

But see how different it is from this?

I need BOTH total on hand and Total on work order - out to the side (like in this last pic)

[Joseph_Seddik · Accepted solution]

I suggest that you create a slice from your table containing the Subcategory and Location column. This slice will contain unique pairs of Subcategory and Location. Its row filter condition would be:

[_RowNumber] = MIN( SELECT(Table[_RowNumber],
  AND(
    [Sub_Category_Code] = [_ThisRow].[Sub_Category_Code],
    [Location_Code] = [_ThisRow].[Location_Code]
  )
))

The columns of this slice should be: "Sub_Category_Code", "Location_Code", also "On hand" and "On Work" virtual columns where the Add and Count are calculated. 

You should then create a new view based on the slice not the table, and group this slice view by the "Sub_Category_Column". This should give you the desired view. 

[Tiger1]

Thank you!  This worked PERFECTLY!

[Tiger1]

I have been using this as a slice - but recently my app wont load.  I noticed this expression is causing it to time out.  Any other ways to do the same result?

[Joseph_Seddik]

I wouldn't conclude that a single slice would time-out the app. In any case, check the performance monitor in your app and see what does it tell you. 

Also, please read this guides: 

• Improve the speed of Sync - AppSheet Help 
• Monitor app performance using Performance Profile - AppSheet Help 
• App performance: Core concepts - AppSheet Help 
• Improve the speed of Sync with database updates - AppSheet Help