Forum Discussion
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) ...
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] )
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]
)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!
Thanks Jihwan_Kim , I will give it a try and let you know how it goes!
