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

#### Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Our team will be reviewing posts using the new "Copilot" label to ensure we highlight and amplify the most relevant and recent content, so you're assured of high-quality content every time you visit. If you share a post that gets featured in the curated gallery, you'll get a PM in the Community to let you know!The curated gallery is ready for you to experience now, so visit the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community. We can't wait to see what you "cook" up!