How to take value from one table and create another column in another table?

[Sean500]

Hi

I have two tables below

Table 1: Sold

LOCATION	sold qty
AA06	0
AA06	0
AA04	8
AA05	4
AA06	23
AA06	0
AA06	1
AA06	0

Table 2: Inventory

Total Inventory	Location
10	AA01
22	AA02
22	AA03
15	AA04
25	AA05
22	AA06

What I want is to find the remaining quantity (Total Inventory - Total Sold by location)

I need am expecting below result

Location	sold qty	Remaining
AA06	0	-2
AA06	0	0
AA04	8	7
AA05	4	21
AA06	23	0
AA06	0	0
AA06	1	0
AA06	0	0

Here Location "AA06" appears many times, so first step is to sum the sold qty for entire "AA06" location which is 24 (0+0+23+1+0+0). Then remaining = Total Inventory - Sold Qty. Same goes to other location

I am not sure how to do this, can anyone advise me please?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how to differenciate AA06s in the Sold table. Perhaps by employee? Or, by customer? 

In my sample, I tried to differenciate those by index number.
please check the below picture and the attached pbix file.

It is for creating a new column in the Sold table.

Remaining CC =
VAR _firstindexnumber =
    MINX (
        FILTER ( Sold, Sold[LOCATION] = EARLIER ( Sold[LOCATION] ) ),
        Sold[Index]
    )
VAR _soldtotal =
    SUMX (
        FILTER ( Sold, Sold[LOCATION] = EARLIER ( Sold[LOCATION] ) ),
        Sold[sold qty]
    )
VAR _inventory =
    SUMX (
        FILTER ( Inventory, Inventory[Location] = Sold[LOCATION] ),
        Inventory[Total Inventory]
    )
RETURN
    SWITCH ( TRUE (), Sold[Index] = _firstindexnumber, _inventory - _soldtotal, 0 )

[Sean500]

HI @Jihwan_Kim Thanks for your reply. Sorry. I forgot to mention. Sold and inventory table is connected with Location column from both the tables. Will the above solutions works for connecting with location? Sorry I don't have index numbers.