Transaction Table to Current Inventory

[07gt]

Hello, 

I'm very new to PowerBI and I'm looking for help turning my transaction table into a current inventory table. See transaction data in picture here:

I have two bins (Bin1 and Bin2) which contain product and I can't figure out how to write a table formula to summarize the quantity in each. Essentially the formula should be Sum(Quantity To BinX) - Sum(Quantity From BinX), but I don't know how to write it in DAX. I need an output table that looks like this:

Any help/guidance would be much appreciated!

Thanks,

Jason

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _ToTable =
    GROUPBY (
        FILTER ( Data, Data[To] <> "Sales" ),
        Data[To],
        "@Qty", SUMX ( CURRENTGROUP (), Data[Quantity] )
    )
VAR _FromTable =
    GROUPBY (
        FILTER ( Data, Data[To] = "Sales" ),
        Data[From],
        "@Qty", SUMX ( CURRENTGROUP (), Data[Quantity] )
    )
VAR _ResultTable =
    ADDCOLUMNS (
        _ToTable,
        "@fromQty", SUMX ( FILTER ( _FromTable, Data[From] = Data[To] ), [@Qty] )
    )
RETURN
    SELECTCOLUMNS (
        SUMMARIZE (
            ADDCOLUMNS ( _ResultTable, "@ResultQty", [@Qty] - [@fromQty] ),
            Data[To],
            [@ResultQty]
        ),
        "Bin", Data[To],
        "Quantity", [@ResultQty]
    )

[07gt]

@Jihwan_Kim  this worked perfectly, thanks so much! The actual inventory table has a lot more detail, so ill adapt what you've written here to filter the way I need to. Appreciate it!

[07gt]

Thanks @Jihwan_Kim , I will give it a try and let you know how it goes!