Forum Discussion

07gt's avatar
07gt
New Member
3 years ago
Solved

Transaction Table to Current Inventory

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

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      07gt
      New Member
      3 years ago

      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!