Re: Stock, Product Sets and M2M. Design and DAX Is...

zztxcxc · ‎11-03-2020

Hello!

The issue I'm trying to resolve is proper modeling for calculating current stock in product sets and it's a bit challenging. (We also have Sales model but it's much simpler to calculate sales in product sets there).

In our inventory model we have stocks calculated using several fact tables (inventory, transit, reserve). They are pretty similar in terms of structure, but to get available stock we have to calculate a measure, that combines data from all of them (for example, stock = inventory - transit - reserve). All tables are daily snapshots of goods.

Now business wants not only to see abailable stock/transit/reserves, but also be able to recalculate them in sets of products depending on current filters.

A set of products is when you combine several products into a single package and sell it as a single unit. So supply department want to know how many sets they can get from the warehouse. Stocks are calculated in a way that we can distinguish products with the same key from different sets (i.e. there is a Product A and it can be in different product sets: set "Type 1" and set "Type 2". When stocks are calculated, whe know that X units could be combined in set "Type 1" and Y units could be combined in set "Type 2", so X+Y will be total stock abailable).

Before we move to Inventory model, let me explain how I calculate product sets sold in Sales Model: it works quite simple - as you can't sell a part of a set, we have the model created like this:

Product Set Bridge table is a combination of Product, Set and Units_Qty_Per_Set. For example:

Set_Key	Product_Key	Qty_Per_Set
Set A	11111	1
Set A	22222	3
Set A	3333	1

So as you can see from the table, Set A contains 5 products. In sales we know Product_Key, Set_Key and QTY sold, so we can calculate the number of product sets sold.

Measure is calculated this way:

Sales in Product sets:= 
   SUMX (
            ADDCOLUMNS (
                VALUES ( 'Product Set'[Set_Key] );
                "Numer of sets";
                    CALCULATE (
                        MINX (
                            ADDCOLUMNS (
                                SUMMARIZE (
                                    'Sales';
                                    'Product Set'[Product_Key];
                                    'Product Set'[Qty_Per_Set]
                                );
                                "Min_Sets"; DIVIDE ( [Sales Quantity] ; [Qty_Per_Set] )
                            );
                            [Min_Sets]
                        )
                    )
            );
            [Numer of sets]
        )

(to protect ourselves we calculate the minimun quantity within each set and them sum up the minimal values for each set just in case)

However, this formula in Inventory model does not work and this is why:

1) You can have stock quantity that cannot be combined in a whole number of sets (i.e. we can 3 units of each product from Set A from the bridge table above available, which means we can ge one full product set and 2 units of each product 11111 and product 22222 left without being combined in any set.

2) We should calculate sets quantity depending on current filters. Let's say product 11111 and 22222 are stored in Canada and product 3333 is stored in Italy. If a user filters by country, than he/she should see 0/blank(), because you can't get a full set within current filter contex

3) This is more coplex one: when users split the measure by product, the still should see the number of sets, that we can get taking into account all products from current set AND current filter context. Let's take a look how it should be:

We have stock:

Product 11111 - 3

Product 22222 - 3

Product 3333 - 3

If we split [Stock In Sets] measure by sets and products, we should see the results:

Set	Product	Stock_In_Sets
Set A	11111	1
	22222	1
	333	1

So, as you can see, even within a single product you should take into account the rest products of a set.

So my first attempt was to design it the same way that it is in Sales Model (this will be just an example of that I made for testing purposes):

After a while I came up with a measure, which is quite complex, but solves the problem

Stock in Sets:=
CALCULATE(
	VAR SetsFilteredByProduct =
	SUMMARIZE(
		FILTER(
			'Bridge_Product_Set_Product';
			'Bridge_Product_Set_Product'[Product_Key] IN Values(Dim_Product[Product_Key])
		)
		;'Bridge_Product_Set_Product'[Product_Set_Key]
		
	)
	VAR SetsWithAllProducts=
		SUMMARIZE(
			FILTER(
				all('Bridge_Product_Set_Product')
				;'Bridge_Product_Set_Product'[Product_Set_Key] in SetsFilteredByProduct 
			
			);
			'Bridge_Product_Set_Product'[Product_Key]
		)
	VAR Sets =
	CALCULATETABLE(
		 ADDCOLUMNS (
			SUMMARIZE (
			    'Bridge_Product_Set_Product';
		    	'Bridge_Product_Set_Product'[Product_Set_Key];
		        'Bridge_Product_Set_Product'[Product_Key];
		        'Bridge_Product_Set_Product'[Product_Quantity_Per_Set]
		        );
		    "Sets_Qty";
		        QUOTIENT (
		            [Stock];
		            'Bridge_Product_Set_Product'[Product_Quantity_Per_Set]
		        )
		)
		;FILTER(
			ALL('Bridge_Product_Set_Product');
			'Bridge_Product_Set_Product'[Product_Set_Key] IN SetsFilteredByProduct	
		)
		;FILTER
		(
			ALL('Dim_Product')
			;'Dim_Product'[Product_key] IN SetsWithAllProducts
		)
	)
	VAR MinBySet = 
	  GROUPBY (
	            Sets;
	            'Bridge_Product_Set_Product'[Product_Set_Key];
	            "Min Sets"; MINX ( CURRENTGROUP (); IF(ISBLANK([Sets_Qty]);0; [Sets_Qty]) )
	        )
	VAR TotalSets = 
	SUMX (
		MinBySet;
		[Min Sets]
	)
	VAR Result = IF(TotalSets = 0; BLANK(); TotalSets)
	return
		Result
)

However, there is a case, when this measure does not work (another set of examples):

The question is: should I somehow rewrite the measure to make it work (no idea how) or change the design to something like this (put bridge table between facts and dim.product):

But this leads us to another set of problems:

1) Our Dim.Product is SCD Type 2, so we'll have to generate all combinations of product version within a set

2) There are products that are not in any set, so we'll have to emulate this.

It seems to me that it's a M2M issue, but I can't cope with it yet