Solved: How to get the sum /count of rows with a value but... - Page 2

Tiger1 · 04-18-2022 09:23 AM

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

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.

View solution in original post

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