Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Stock, Product Sets and M2M. Design and DAX Issues

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Stock, Product Sets and M2M. Design and DAX Issues

11-03-2020
04:13 AM

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

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-03-2020
05:10 AM

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

Proud to be a Datanaut!

Announcements

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

290 | |

131 | |

126 | |

106 | |

87 |

Top Kudoed Authors

User | Count |
---|---|

300 | |

171 | |

144 | |

116 | |

113 |