creating table help

[erhan_79]

Hi there ;

İ nned your kind help abou below issue pls , I have two table Table A and Table B,

• Table A gives stock quantities based on warehouse location and material ,
• Table B gives delivered quantities based on warehouse location  and material , You can see below that 2 tables 

so i would like  to take difference between stock and delivered quantities but based on specific 3 warehose locations . These locations are "Location1" , "Location2" , "Location3" .I want to create a new table which is calling " Table C " , will give me difference of the stock quantity and delivered Quantity between Table A and Table B .You can see sample created Table C that i wanted as below .Also i am sharing a link about sources as below to make your job easy :).Thanks in advance dears.

 https://drive.google.com/file/d/1ii6MU0tOx2V9kjivL8DJW8U_z2TJlPD-/view?usp=sharing

[AlB · Accepted solution]

@erhan_79 

Or if you wanted to create the table in DAX:

TableC V2 = 
SUMMARIZECOLUMNS (
    TableA[Material],
    TableA[Warehouse Location],
    "Difference quantity",
        VAR aux_ =
            SUM ( TableA[Stock Quantity] )
                - CALCULATE (
                    SUM ( TableB[Delivered Quantity] ),
                    TREATAS (
                        SUMMARIZE ( TableA, TableA[Material], TableA[Warehouse Location] ),
                        TableB[Material],
                        TableB[Warehouse Location]
                    )
                )
        RETURN
            IF ( aux_ = 0, BLANK (), aux_ )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @erhan_79 

You can do this best in Power Query. Place the following M code in a blank query to see the steps for Table C (it assumes TableA and TableB in place as shown above):

let
    Source = Table.NestedJoin(TableA, {"Material", "Warehouse Location"}, TableB, {"Material", "Warehouse Location"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Delivered Quantity"}, {"Delivered Quantity"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "Difference quantity", each [Stock Quantity]-[#"Delivered Quantity"], type number),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Difference quantity] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Stock Quantity", "Delivered Quantity"})
in
    #"Removed Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.