Measure for production Bom table to calculate the requirment sub-items for multipack items

[FredBus]

Hi @all,
i can´t solve my problem. Maybe somone can help?
Given Dataset:
Table: stock_v2 with columns: [quantity] [item number OF/UV] Table: Item with columns: No_ Base Unit of Measure Table: Production BOM Line with columns: Line No_ No_ Quantity No_ item Table: Sales Line with columns: Document No_ No_ Outstanding Qty_ (Base) Requested Delivery Date Table: Date with columns: Date Day Month Year IsoKW #

Active relationships: From Production BOM Line [No_item] to Item [No_]: many to one From Sales Line [No_] to Item [No_]: many to one From stock_v2 [item number OF/UV] to Item [No_]: many to one From Sales Line [Requested Delivery Date] to Date [Date]: many to one

Inactive relationships: From Production BOM Line [No_] to Item [No_]: many to one

Multipack items are defined in the Item table as follows: if Base Unit of Measure <> "STK" , then the item is a multipack item. The Production BOM Line contains information about which individual items in column [No_] belong to which multipack item in column [No_item]. The [Quantity] column indicates the number of individual items in the multipack item. An individual item can exist in multiple different multipack items.

This measure calculates the demand for multipacks from the Sales Line table:

Target demand MP =
VAR orderedQuantity =
CALCULATE(
SUM('Sales Line'[Outstanding Qty (Base)]),
LEFT('Sales Line'[Document No_], 2) = "AU",
FILTER('Production BOM Line', 'Production BOM Line'[No_ item] = SELECTEDVALUE('Item'[No_]))
)

VAR _FinishedGoodsStockMP =
CALCULATE(
SUM(stock_v2[quantity]),
FILTER(RELATEDTABLE('Item'), 'Item'[Base Unit of Measure] <> "STK")
)

RETURN
IF(
(_orderedQuantity - _FinishedGoodsStockMP) <= 0,
BLANK(),
_orderedQuantity - _FinishedGoodsStockMP
)

The result of this measure needs to be multiplied by the Quantity column of the sub-items.
Example BOM Line Table

Line No_	No_	Quantity	No_ item	requirement quantity No_item	Requirement quantity sub-item for MP
20000	6500	3	6504	8940	26820
30000	6505	3	6504	8940	26820
40000	6501	3	6504	8940	26820
10000	6400	3	6504	8940	26820

Example Item Table

No_	Base Unit of Measure
6500	STK
6505	STK
6501	STK
6400	STK
6504	MP

Example Sales Line Table

Document No_	No_	Outstanding Qty_ (Base)	Requested Delivery Date
AU-22009	6500	13686	01.10.2023 00:00
AU-22009	6505	10710	01.10.2023 00:00
AU-22009	6501	11700	01.10.2023 00:00
AU-22009	6400	6912	01.10.2023 00:00
AU-22009	6504	9990	01.10.2023 00:00
AU-22010	6500	5850	10.10.2023 00:00
AU-22010	6505	3456	10.10.2023 00:00
AU-22010	6501	3456	10.10.2023 00:00
AU-22010	6400	20	10.10.2023 00:00

Example Stock Table

no_	quantity
6500	1050
6505	1050
6501	1050
6400	1050
6504	1050

The result should looks like below:

Filter: Item (Base Unit of Measure)= STK

No_	Base Unit of Measure	Order quantity item	Order quantity sub-item for MP	Stock	requirement quantity item	Requirement quantity sub-item for MP
6500	STK	19536	26820	1050	18486	26820
6505	STK	14166	26820	1050	13116	26820
6501	STK	15156	26820	1050	14106	26820
6400	STK	6932	26820	1050	5882	26820

[some_bih]

Hi @FredBus Intermediate table is wanted output? If yes, from which table and columns values should come from? 

Two columns in this table have the same values: Order quantity sub-item for MP and Requirement quantity sub-item for MP, is it by luck or ...

[FredBus]

@some_bihi'm sorry for the misunderstanding, you can delete the intermediate table. To cover the demand for multipacks, you can obtain the sums of individual items through the following calculation:

The result from "Target demand MP" should then be indicated as an additional column in the table visual. In the original file, the calculation was solved using calculated columns, where all the required quantities were summed up as calculated columns in the Item table. However, I need a dynamic solution for demand calculation.
This is my calculated column in the  item table from original file. "I can't replicate the issue in the example file because it's resulting in a circular failure, and I'm unsure of the cause.

My wanted output is this table visual: I need to see in the table visual the individual item quantities required for single-variety items (Column Target deman item) and the individual item quantities required for multipacks.(Column MP)

[some_bih]

Hi @FredBus I was thinking about your model, as much as I could understand. Following is concerning your circular reference: it could be that some connection is wrong. As I understand, given that your table are tiny, which is good, you should remodel your model using Power query transofrmation if possilbe, and after that make connection, check results. Example, Item table and Stock table are just few column. I aslo understand that there could be "need" to see data on Document level so you have table Sales line or something. I am just saying, build your model step by step, and use as much model as you need, not single one. Hope this help

[FredBus]

Hi @some_bih , thanks for your response.
I need a report that shows the required quantities of an item as individual units and the required quantities as part of a multipack (where a multipack consists of multiple different individual items). I have already solved this demand calculation using calculated columns in my original file, but unfortunately, I cannot share the data as it is highly sensitive. However, the current report no longer meets the requirements of our planning, so I would like to represent each calculation using dynamic measures. For example, to filter the required quantities by calendar weeks.

[some_bih]

Hi @FredBus Honestly I do not fully understand your model so I am not sure how to contribute as calculation logic is unknown for me. Concerning week view, the best would be to implement Date / Calendar table, if you do not have it already.

Week number is usually obtained using funcion. Hope this help

https://learn.microsoft.com/en-us/dax/weeknum-function-dax 

[FredBus]

@some_bihokay... i will resolve the circular issue and update the example file with multiple examples.

[FredBus]

@some_bihi will make an example file. Where can I upload the file?

@tamerj1"I'm sorry for asking, but last time you were able to solve my problem very well... Do you have any ideas?"

[some_bih]

Hi @FredBus if you have office 365, just send shared link

[FredBus]

Hi @some_bih thx. Follow the link to download my power bi example file.
https://wetransfer.com/downloads/20daad434028a810bc1893f9064198ef20230629085907/63595daa9f5c1a3f761e...

[some_bih]

Hi @FredBus share file thx