Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
unclejemima
Post Patron
Post Patron

Sum inventory quantity in another table

I've got a table called STOK and one called INVENTORY.  Each table has a record called InvUnq what is the inventory unique number.

 

The STOK table has multiple lines in it showing the stock transactions.

The INVENTORY table has only one line for each inventory item/


I'm wanting to make a column in the INVENTORY table that has the total stock level based off the multiple lines in the STOK table.

 

Example.

STOK table

InvUnq = 1 , Qty = 5

InvUnq = 1, Qty = 4

 

on the INVENTORY TABLE I want to make new column called "total stock" that would add the qty of both lines (they are the same part as the unique number is 1 for both of them).

 

How to do I do a sum based on totaling all the parts in the STOK table to display as one number in the INVENTORY column called Total Stock I just made?

Thanks!!!

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

due to the fact that the INVENTORY table has on line per item you can leverage the power of relationships. Create a relationship between the table INVENTORY on the one side and the table STOK on the many side like so:

image.png

This allows to create a calculated column inside the INVENTORY table using this DAX statement:

Column = CALCULATE(SUM('STOK'[Qty]))

Hopefully this is what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey,

 

due to the fact that the INVENTORY table has on line per item you can leverage the power of relationships. Create a relationship between the table INVENTORY on the one side and the table STOK on the many side like so:

image.png

This allows to create a calculated column inside the INVENTORY table using this DAX statement:

Column = CALCULATE(SUM('STOK'[Qty]))

Hopefully this is what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks!

 

Is is possible to do this as column not a measure?

Hey,

 

as I stated just create a calculated column in the inventory table:

image.png

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

My appologies, I misread.  Thank you so kindly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.