Structuring and grouping data

[Anonymous]

Hello all,
I have a parts list with materials, which in turn can consist of many different components.
I have now made various transformation steps to show the path. So I can see which material consists of which components.
Now I have to calculate the manufacturing costs with the data.
My idea now is to link the last component with prices, so that I can then calculate costs here again based on the quantities of the higher levels.

The area marked in yellow below shows, for example, that the component ..68070100 consists of two parts.
Now I have to bring the quantities 731 and -253 to the lower level. These are the number of parts that go into the higher-level component.
Does anyone have an idea how I can do this? Maybe through a calculation that takes the level and type into account?

[Anonymous]

Thank you for your answer.

I get the following error message:

Calculation error in Measure "Called function"[formula]: The arguments in the GenerateSeries function must not be empty.

Can you tell me what I am doing wrong?

[Anonymous]

HI @Anonymous ,

I'd like to suggest you use the path string with GENERATESERIES function to expand the text values to list. Then you can use the item list to filter on your table to summary correspond records based on current product code path index:

formula =
VAR currProduct =
    SELECTEDVALUE ( 'Table'[ProductCode] )
VAR _pathExplode =
    SELECTEDVALUE ( 'Table'[PathExplode] )
VAR _length =
    PATHLENGTH ( _pathExplode )
VAR _pathtable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, _length, 1 ),
        "Product", PATHITEM ( _pathExplode, [Value] )
    )
VAR _pathIndex =
    MAXX ( FILTER ( _pathtable, [Product] = currProduct ), [Value] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [ProductCode]
                IN SELECTCOLUMNS (
                    FILTER ( _pathtable, [Value] >= _pathIndex ),
                    "Product", [Product]
                )
        )
    )

Regards,

Xiaoxin Sheng