Review My Expression?

Hi there would someone review my expression to see where I am going wrong?

SUM (SELECT(Loads[Bale Quantity], Loads[Order Reference Column]=Orders[Order ID], True))

I have 2 tables, Loads and Orders. Loads are a nested table/children of Orders. One column in table “Loads” is Bale Quantity, and I would like a sum for all of the bale quantities associated with a certain order. Loads has a reference column called Order Reference Column that matches each load to an order by the Order ID (Order Id=Order Reference Column for a Parent order and a Child Load)

The expression assistant reads me back the following for the above expression:

SUM(
…The list of values of column ‘Bale Quantity’
…from rows of table ‘LOADS’
…where this condition is true: ((LOADS[Order Reference Column]) is equal to (ORDERS[Order ID])))

This seems to be exactly what I want, but the expression gives me 0 no matter what the column Bale Quantity has for its values. Any help??

Where are you attempting to put this Expression? Because right now, you’re getting a 0 because you are asking, “Does the list of everything from [Order Reference Column] in Loads match the list of everything from [Order ID] in Orders?” Which will never be true in this instance. You probably just want SUM(SELECT(Loads[Bale Quantity], [Order Reference Column]=[Order ID], True)).

2 Likes

I’m putting it into the app formula auto compute section of column “total Bales” in the Table Orders.

Thanks for the help again! That worked like a charm.

2 Likes