Stocks redistribution Table

[D3K]

Hello everyone!

I have a really compliсated task to make it by myself, believe I've tried 🙂  so asking for help here.

We have a database with Stores, Products, Sales History and Current Stocks.

The goal is to redistibute stocks from stores WITH stocks and NO sales to stores WITHOUT stocks and WITH sales in the past.

Please see the the attached image.

For example, as you can see in Store 1 we have 5pcs of Product A and no sales. So we need to move it to Stores 2 and 4, where there is no stocks, but these stores needs Product A, cause they can sell it. 

And also we need to do nothing for Store 3, cause it has both of sales and stocks.

So I need to make a visual table like on the image below

Any help will be highly appreciated. Thanks a lot!

[Anonymous · Accepted solution]

Here's the solution:

You use the Filter Pane of the table above with the [Is True Target Store] measure to obtain the above.

And here's what needs to be created in PQ behind the scenes:

// SalesAndStockBaseTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVMDBU0lFyBGIgMlWK1YGLG0HFjUFySOLGUHEjdA0mSBLI4qYIC8yRxc2AAk4QcWRhc6iwKZq4BUK5oRGyhCVWcwwNEMLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, ProductID = _t, #"Sales Qty" = _t, #"Stock Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", type text}, {"ProductID", type text}, {"Sales Qty", Int64.Type}, {"Stock Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Store Type", 
        each 
        let
            StockQty = if [Stock Qty] = null then 0 else [Stock Qty],
            SalesQty = if [Sales Qty] = null then 0 else [Sales Qty],
            Output = 
                if StockQty > 0 and SalesQty = 0 then "Source Store" 
                else 
                if StockQty = 0 and SalesQty > 0 then "Target Store" 
                else 
                    "Neutral Store"
        in
            Output
    )
in
    #"Added Custom"

// Stores
let
    Source = SalesAndStockBaseTable,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Store ID", "Store Type"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Products
let
    Source = SalesAndStockBaseTable,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ProductID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Target Stores
let
    Source = Stores,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Store Type] = "Target Store")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Store Type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Store ID", "Target Store ID"}})
in
    #"Renamed Columns"

// Sales and Stock
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVMDBU0lFyBGIgMlWK1YGLG0HFjUFySOLGUHEjdA0mSBLI4qYIC8yRxc2AAk4QcWRhc6iwKZq4BUK5oRGyhCVWcwwNEMLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, ProductID = _t, #"Sales Qty" = _t, #"Stock Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", type text}, {"ProductID", type text}, {"Sales Qty", Int64.Type}, {"Stock Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Store Type", 
        each 
        let
            StockQty = if [Stock Qty] = null then 0 else [Stock Qty],
            SalesQty = if [Sales Qty] = null then 0 else [Sales Qty],
            Output = 
                if StockQty > 0 and SalesQty = 0 then "Source Store" 
                else 
                if StockQty = 0 and SalesQty > 0 then "Target Store" 
                else 
                    "Neutral Store"
        in
            Output
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Store Type"})
in
    #"Removed Columns"

[Anonymous · Accepted solution]

Current Stocks = 
var StoreInScope = ISINSCOPE( Stores[Store ID] ) 
var ProductInScope = ISINSCOPE( Products[ProductID] )
var BothInScope = StoreInScope && ProductInScope
var Result =
    if( BothInScope,
        sum( 'Sales and Stock'[Stock Qty] )
    )
return
    Result

Is True Target Store = 
var SourceStoreInScope = ISINSCOPE( Stores[Store ID] )
var SourceStoreIsReallySource = 
    SELECTEDVALUE( Stores[Store Type] ) = "Source Store"
var TargetStoreInScope = ISINSCOPE( 'Target Stores'[Target Store ID] )
var TargetStoreNotBlank =
    // It could be blank due to the additional row
    // generated by DAX in the Target Stores table
    // after enabling the relationship which has
    // referential integrity violations (but this is
    // how it should be since not all stores are
    // target stores).
    NOT ISBLANK( SELECTEDVALUE( 'Target Stores'[Target Store ID] ) )
var ProductInScope = ISINSCOPE( Products[ProductID] )
var AllThreeInScope = TRUE()
    && SourceStoreInScope
    && SourceStoreIsReallySource
    && TargetStoreInScope
    && TargetStoreNotBlank
    && ProductInScope
var IsCurrentTargetStoreForCurrentProduct =
    if( AllThreeInScope,
        CALCULATE(
            NOT ISEMPTY( 'Sales and Stock' ),
            // Remove filters from Stores
            ALL( Stores ),
            // Enable filtering from the Target Stores
            USERELATIONSHIP(
                'Target Stores'[Target Store ID],
                'Sales and Stock'[Store ID]
            )
        )
    )
return
    // I multiply by 1 to return an int
    // so that the Filter Pane can work
    // with this measure.
    1 * IsCurrentTargetStoreForCurrentProduct

[D3K]

Thanks a lot! It works in the best way!

[Anonymous · Accepted solution]

Here's the solution:

You use the Filter Pane of the table above with the [Is True Target Store] measure to obtain the above.

And here's what needs to be created in PQ behind the scenes:

// SalesAndStockBaseTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVMDBU0lFyBGIgMlWK1YGLG0HFjUFySOLGUHEjdA0mSBLI4qYIC8yRxc2AAk4QcWRhc6iwKZq4BUK5oRGyhCVWcwwNEMLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, ProductID = _t, #"Sales Qty" = _t, #"Stock Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", type text}, {"ProductID", type text}, {"Sales Qty", Int64.Type}, {"Stock Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Store Type", 
        each 
        let
            StockQty = if [Stock Qty] = null then 0 else [Stock Qty],
            SalesQty = if [Sales Qty] = null then 0 else [Sales Qty],
            Output = 
                if StockQty > 0 and SalesQty = 0 then "Source Store" 
                else 
                if StockQty = 0 and SalesQty > 0 then "Target Store" 
                else 
                    "Neutral Store"
        in
            Output
    )
in
    #"Added Custom"

// Stores
let
    Source = SalesAndStockBaseTable,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Store ID", "Store Type"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Products
let
    Source = SalesAndStockBaseTable,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ProductID"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

// Target Stores
let
    Source = Stores,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Store Type] = "Target Store")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Store Type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Store ID", "Target Store ID"}})
in
    #"Renamed Columns"

// Sales and Stock
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVMDBU0lFyBGIgMlWK1YGLG0HFjUFySOLGUHEjdA0mSBLI4qYIC8yRxc2AAk4QcWRhc6iwKZq4BUK5oRGyhCVWcwwNEMLGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Store ID" = _t, ProductID = _t, #"Sales Qty" = _t, #"Stock Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", type text}, {"ProductID", type text}, {"Sales Qty", Int64.Type}, {"Stock Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Store Type", 
        each 
        let
            StockQty = if [Stock Qty] = null then 0 else [Stock Qty],
            SalesQty = if [Sales Qty] = null then 0 else [Sales Qty],
            Output = 
                if StockQty > 0 and SalesQty = 0 then "Source Store" 
                else 
                if StockQty = 0 and SalesQty > 0 then "Target Store" 
                else 
                    "Neutral Store"
        in
            Output
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Store Type"})
in
    #"Removed Columns"

[D3K]

Thanks a lot for this elegant solution!

But I've missed 2 measures, used in this algorithm: "Current Stocks" and "Is True Target Store"

Can you add it here? Thank you 

[Anonymous]

OK, I think I know what you mean now.

[Anonymous]

You should be more detailed in your description of the algorithm to cover all possible cases.

[D3K]

I'll try

If a store has zero sales of Product A and has stocks of Product A more than 0, we need to take it's stocks and move it to the stores, which needs Product A - has sales of that Product X more than 0 and zero stocks. 

So we need to see the list of such stores (which has sales and no stocks) 

For example (please see the table DataBase on the image above):

1. We have a Store 1, where Product A has no sales and stocks in quantity of 5pcs.
2. We have Store 2 and Store 4, where sales of Product A are more than 0 and zero stocks (marked red)

3. We need to get the list of stores (point 2), where we can move Product A from Store 1 (point 1).

Store 1 is a source of Product A, Stores 2 and 4 are the target for Product A.

It's kind of recomendation, that "You can move Product A to Store 2 or Store 4, because they need this Product A".

User need to see next information:

Store 1 - Product A -> Store 2
Store 1 - Product A -> Store 4

Like on the image above (right table "Move products" - this is what I need in the final).

Please let me know, if I can give more information

Thanks