Calculate the sum of a column after filtering the rows related to the current row

[Hadi_Abdulwahab]

I have two tables, the first one contains items in stock, the second one entries of each item…

Table 1 (item, count)
Table 2 (item “related to table 1 item”, sub_count, price)

in table 1, the count column should filter table 2 rows which has (item) as the current row, and then sum the sub_counts

I thought this should work, but instead, it gives me 0 or the sum of all rows

Table2.Filter(Table2.item.contains(thisRow()).sum(sub_count)

and why can’t I use Table2.item=thisRow() instead since I deselected the multiple entries option?

[Rohan_Mitchell]

Hi @Hadi_Abdulwahab,

There are a couple of issues with your formulas as they are presented.

“ThisRow” does not require parenthesis after, and the position of “sub_count” within the .Sum is incorrect. You could try this -

Table2.Filter(Table2.Item = ThisRow).Item.Sum()

Because you are filtering on Table2, the “.Item” after the Filter formula will refer to the Item column from Table2.

If that doesn’t work then it would help if you provide a screenshot or two, or even embed a copy of your doc for the community to look at (a copy with any private data removed of course).

Rohan

[Hadi_Abdulwahab]

Column 3 in Items should be
14
12
0

[Hadi_Abdulwahab]

[Hadi_Abdulwahab]

I tried this, which I thought would work, but I got the same result (26 or 0)

[Invoice 1].Filter([Invoice 1].Item.Contains(thisRow)).Number.Sum()

[Rohan_Mitchell]

The problem is “[Invoice 1].Item” returns a list of all values in the Item column of the Invoice 1 table. Thus your formula is indeed summing all values in the table because of this.

When you look at the “chips” in a formula, there is a small icon at the right-hand end that indicates this -

The icon looks like one row on top of another. This indicates there is more than one value in the result that that chip represents.

If you remove “Invoice 1” from just that part of the formula, it will/should work. It should look like this -

Notice the change in the icon, it is now indicating just a single row -

This is something you will find helpful in your formulas as it will often reveal why a formula isn’t working.