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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (87,432)