Total all Parent to Child Order Costs

DAX
1

I have a complex issue of calculating the costs of Parent to Child Orders.

The following image shows the Parent to Child table.

Parent to Child Orders

The next image shows the Order Costs table, here we show the order number, operation number and cost for each operation.

Order Costs

Next I show an image of the costs associated to each order, however the costs returned are for the maximum operation number associated to each order.

Order Costs Per Max OpNo

What I need to do is show the total costs for each Parent Order & associated rework orders (or child orders), similar to the image below.

Actual Total Costs

I used the following calculated column in the Parent to Child table to concatenate all orders per row.

HierarchyColumns = 
VAR OrderNumber =
    [Original_ProdOrder_Number] & "-" &
    [Rework Order-1] & "-" &
    [Rework Order-2] & "-" &
    [Rework Order-3] & "-" &
    [Rework Order-4] & "-" &
    [Rework Order-5] 
   
VAR LastNonDashPosition =
    MAXX (
        GENERATESERIES (1, LEN(OrderNumber)),
        IF (
            MID (OrderNumber, [Value], 1) <> "-",
            [Value],
            BLANK()
        )
    )

RETURN
    LEFT (OrderNumber, LastNonDashPosition)

So I was hoping to create a DAX measure using the HierarchyColumn to determine the total costs for each Parent and associated Child order, by getting the Maximum Op No costs for each Parent & Child association.

Any help would be appreciated.
JR

BI and Excel file attached.

Parent to Child Costs.pbix (31.2 KB)
Parent to Rework Orders.xlsx (12.0 KB)

2

Answer

As no suggestions came forward, I ventured along a different path and solved my problem with another technique.

This required me to unpivot the Parent to Child table, leaving Parent Order and 1 Rework Order column. I then referenced the revised table to gather unique records of Parent Order in a separate table. I then used the unique Parent Order table to create the desired relationships to the unpivoted Parent to Child table. Then wrote the required DAX measures to satisfy my requirements.

Thanks
JR

3

Hi @jprlimey ,

Welcome back to Forum.

I suppose you miss the transformation Parent to Child table to have parent and child in separate columns

  • Add custom column - with additional Parent (you need a twist and also have a parent child in child column too - so you have right calculation afterwards)

  • Click on Parent Column and Click Unpivot others

  • Remove Additional Column that you not need and Rename Child Order column

Second, you need to add relationship from Order Costs - Order to this new Child Order column

And here it is:
image

You can check
Parent to Child Costs v1.pbix (28.3 KB)

Additional you can add Hierarchy Column and additional formatting if you need.

Hope it helps.