cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
1000ghz
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)

warehousematerial_codeqty
10a15
10b15
20a10

 

TABLE_2 (static table)

material_codeprice $
a1
b2
c3
d4

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Suchitra1996
Resolver II
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.

Suchitra1996_0-1614954082661.png

 

 

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. 

View solution in original post

5 REPLIES 5
Suchitra1996
Resolver II
Resolver II

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

Suchitra1996_0-1614949510995.png

 


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

1000ghz_0-1614950786715.png

 

Suchitra1996
Resolver II
Resolver II

@1000ghz 

 

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

@Suchitra1996  of course 😉

Suchitra1996
Resolver II
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.

Suchitra1996_0-1614954082661.png

 

 

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. 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (957)