Trouble calculating cost for kits

[Pingu]

I have some kits that include several pieces like this:

Then I have a table with orders. I buy the pieces in kits but I can sell them individually, thus in my orders I find only pieces like this:

So in order to know the cost of my orders I need to take the max Qty of any piece for each kit, since in order to deliver the order I need to use than many kits (despite I have some pieces left that are not ordered I count the whole kit cost since the kit is not complete anymore).

Let's see an example. For the O1 and O2 the cost would be:

in the case of O1, since I need 3 P1.2 I would need to use 3 kits K1 in order to be able to deliver the order. Same situation for K2 where I'd need 5 whole kits K2. So the cost would be: O1= 3*K1 + 5*K2= 3*$10+5*$20= $130

In the case of O2, the cost would be: O2= 5*K1+2*K2= 5*$10+2*$20= $90

But if we consider the whole sells O1+O2 together then we can reuse the leftover parts when combining the orders:

So the cost in this case would be: O1+O2= 5*K1+7*K2= 5*$10+7*$20= $190

I'm stuck with this cost measure, I don't know how can I get the max nr of pieces for each kit

any tip???

[sturlaws · Accepted solution]

It depends a little on how you want to display you data. This measure will give you the price for a single order, if there is only 1 order in the current context. For instance if you have a table visual with order-ids and this measure, it will show the total price for each order. 

But if you have a table without order-id, and select two orders from a slicer, it will show you the price based on summing the number of pieces, and using the max values for calculating the price of the two orders. Ok, I know this is a rubbish explanation, so I have added a demo report at the end, so you can check it out for yourself.

Price combined orders =
VAR _pieces =
    GROUPBY (
        GROUPBY (
            Orders;
            orders[Piece];
            vKits[Kit];
            "numberOfPieces"; SUMX ( CURRENTGROUP (); Orders[QTY] )
        );
        vKits[Kit];
        "maxNumberOfPieces"; MAXX ( CURRENTGROUP (); [numberOfPieces] )
    )
VAR _price =
    ADDCOLUMNS ( _pieces; "auxPrice"; LOOKUPVALUE ( vKits[Price]; vKits[Kit]; [Kit] ) )
RETURN
    SUMX ( _price; [maxNumberOfPieces] * [auxPrice] )

demo

[Pingu]

Thanks Sturlaws!

this is exactly what I was trying to achieve.

Awesome help and thanks for your time and demo.

regards