cancel
Showing results for
Did you mean:
Frequent Visitor

## Filter, Lookup and Sum with elements by two different tables

Dear all,

i'm fighting with an inventory powerapp, i have 2 different tables where i need to do some filters and operations.

Let me explain...

I have 2 different tables

TABLE_1 (live update by powerapp)

 warehouse material_code qty 10 a 15 10 b 15 20 a 10

TABLE_2 (static table)

 material_code price \$ a 1 b 2 c 3 d 4

I need, for each warehouse (table_1), calculate his total value (\$) amount based on how many (qty) parts, for each component (material_code) are stored.

Based on the tables before:

• Warehouse 10
• 15 parts of "material_code" "a" * 1\$ = 15\$
• 15 parts of "material_code" "b" * 2\$ = 30\$
• TOTAL = 15\$ + 30\$ = 45\$
• Warehouse 20
• 10 parts of "material_code" "a" * 1\$ = 10\$
• TOTAL = 10\$

Can you help me to find the correct formula to calculate the warehouse value (\$), please?

1000ghz

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver II

okay, I have made a gallery filtering with Distinct(SDTest2,Warehouse)

Here, SDTest1 and SDTest2 are my SharePoint list but you can replace them both with your respective tables and columns.

Warehouse label has Text property : ThisItem.Result

qty label : Sum(Filter(SDTest2,Warehouse=ThisItem.Result),qty)

Price label: Sum(Filter(SDTest2,Warehouse=ThisItem.Result),qty*LookUp(SDTest1,materialcode2=materialcode1,price))

If you're using anything other than Sum, you can just modify the sum that will accumulate the price from the other table.

5 REPLIES 5
Resolver II

I made a test on my end:

I'm using 2 galleries that is displaying the information that you have posted and displaying the final cost with the help of a label.

On select of next icon of the top gallery, i have collected the items with same warehouse:

``ClearCollect(warehouse,Filter(SDTest2,Warehouse=ThisItem.Warehouse))``

The formula used to calculate the total price:

``Sum(warehouse,qty*LookUp(SDTest1,materialCode2=materialCode1,Price))``

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Frequent Visitor

@Suchitra1996  thnx a lot for your suggestion.

My idea was to have a simple screen where the people can see 3 simple data:

• Warehouse number  >> already done
• Total quantity parts  (sum of all parts by all material codes)  >> already done
• Total price value  >> here my fighting

Could be possible to achive this kind of result?

Resolver II

I'm assuming that you are using a gallery to display the tabular data?

Frequent Visitor

@Suchitra1996  of course 😉

Resolver II

okay, I have made a gallery filtering with Distinct(SDTest2,Warehouse)

Here, SDTest1 and SDTest2 are my SharePoint list but you can replace them both with your respective tables and columns.

Warehouse label has Text property : ThisItem.Result

qty label : Sum(Filter(SDTest2,Warehouse=ThisItem.Result),qty)

Price label: Sum(Filter(SDTest2,Warehouse=ThisItem.Result),qty*LookUp(SDTest1,materialcode2=materialcode1,price))

If you're using anything other than Sum, you can just modify the sum that will accumulate the price from the other table.

Announcements

#### Power Apps Community Call

Next call is happening on April 21st at 8a PST.