Filter, Lookup and Sum with elements by two different tables

(0) Share

Report

Posted on by 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

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Suchitra1996 185 on at

Like (0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
1000ghz 26 on at

Like (0)

Report
Copy link

Link copied!
@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?

Was this reply helpful? Yes No
Suchitra1996 185 on at

Like (0)

Report
Copy link

Link copied!

@1000ghz

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

Was this reply helpful? Yes No
1000ghz 26 on at

Like (0)

Report
Copy link

Link copied!

@Suchitra1996 of course 😉

Was this reply helpful? Yes No
Verified answer

Suchitra1996 185 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No