Filter, Lookup and Sum with elements by two different tables

[1000ghz]

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?

Thnx in advance.

1000ghz

[Suchitra1996]

@1000ghz 

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.

[1000ghz]

@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?

[Suchitra1996]

@1000ghz 

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

[1000ghz]

@Suchitra1996  of course 😉

[Suchitra1996 · Accepted solution]

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.