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

#### Back to Basics Tuesday Tip #9: All About the Galleries

This is the ninth post in our series dedicated to helping the amazing members of our community--both new members and seasoned veterans--learn and grow in how to best engage in the community! Each Tuesday, we feature new content that will help you best understand the community--from ranking and badges to profile avatars, from Super Users to blogging in the community. Our hope is that this information will help each of our community members grow in their experience with Power Platform, with the community, and with each other!     Today's Tip: All About the Galleries Have you checked out the library of content in our galleries? Whether you're looking for the latest info on an upcoming event, a helpful webinar, or tips and tricks from some of our most experienced community members, our galleries are full of the latest and greatest video content for the Power Platform communities.   There are several different galleries in each community, but we recommend checking these out first:   Community Connections & How-To Videos Hosted by members of the Power Platform Community Engagement  Team and featuring community members from around the world, these helpful videos are a great way to "kick the tires" of Power Platform and find out more about your fellow community members! Check them out in Power Apps, Power Automate, Power Pages, and Copilot Studio!         Webinars & Video Gallery Each community has its own unique webinars and videos highlighting some of the great work being done across the Power Platform. Watch tutorials and demos by Microsoft staff, partners, and community gurus! Check them out: Power Apps Webinars & Video Gallery Power Automate Webinars & Video Gallery Power Pages Webinars & Video Gallery Copilot Studio Webinars & Video Gallery   Events Whether it's the excitement of the Microsoft Power Platform Conference, a local event near you, or one of the many other in-person and virtual connection opportunities around the world, this is the place to find out more about all the Power Platform-centered events. Power Apps Events Power Automate Events Power Pages Events Copilot Studio Events   Unique Galleries to Each Community Because each area of Power Platform has its own unique features and benefits, there are areas of the galleries dedicated specifically to videos about that product. Whether it's Power Apps samples from the community or the Power Automate Cookbook highlighting unique flows, the Bot Sharing Gallery in Copilot Studio or Front-End Code Samples in Power Pages, there's a gallery for you!   Check out each community's gallery today! Power Apps Gallery Power Automate Gallery Power Pages Gallery Copilot Studio Gallery