cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zztxcxc
New Member

Stock, Product Sets and M2M. Design and DAX Issues

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:

zztxcxc_0-1604397631204.png

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

Set_KeyProduct_Key Qty_Per_Set 
Set A111111
Set A222223
Set A33331

 

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:

SetProductStock_In_Sets
Set A111111
 222221
 3331

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

image.png

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

image.png

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

image.png

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

1 REPLY 1
CheenuSing
Community Champion
Community Champion

Hi @zztxcxc ,

 

Is it possible to load the pbix file with sample data and output expected to One Drive or Google Drive and share the link here.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors