Calculate delivery costs based on orders rules

[Anonymous]

Hi all,

I just made an example of my data sheet.

In the first colum is the order number started with order 100

This order contains 2 rules for customer A. First rule 1 product 111111 with product description product 1.

Then its possible to deliver the order or let the customer pick it up at the Store.

After all there is a price for each product. (product 1 = 400,68 and product 5 = 27,50)

If the order is delivered and the total price is under the €350 there should be and extra product add to the order called product 99999 delivery charge.

Order 100 is delivered but about €350 so it is not needed to add the extra product.

Order 101 is deliverd and under the total price of €350 so the extra product 999999 is added

Order 102 is deliverd and under the total price of €350 so the extra product 999999 should be added but did not happend. 

Order 103 is not deliverd (store) but the price is under €350 but it is not needed to add the extra product 999999 because it is not deliverd.

So im looking for a dax formula that can calculate the total price of the order and is using the conditions of 350 and delivery status. 

Thanks a lot!

Order	Rule	Customer	Article number	Description	delivery/store	Price	Delivery cost yes or no
100	1	A	111111	Product 1	delivery	€ 400,68	no
100	2	A	555555	Product 5	delivery	€ 27,50	no
101	1	B	777777	Product 7	delivery	€ 75	yes
101	2	B	131313	Product 13	delivery	€ 66,60	yes
101	3	B	232323	product 23	delivery	€ 46,50	yes
101	4	B	999999	Delivery charge	delivery	€ 15	yes
102	1	C	444444	product 4	delivery	€ 85,68	Yes but forgot to add
102	2	C	555555	product 5	delivery	€ 12,80	Yes but forgot to add
103	1	D	111111	product 1	store	€ 64,64	no
103	2	D	777777	product 7	store	€ 13,77	no
103	3	D	161616	product 16	store	€ 41,28	no

[wdx223_Daniel]

@Anonymous  in a calculated column, this formula works well

=
VAR _OrderTotal =
    SUMX (
        FILTER (
            Table4,
            Table4[Order] = EARLIER ( Table4[Order] )
                && Table4[Article number] <> 999999
        ),
        Table4[Price]
    )
VAR _Charge =
    SUMX (
        FILTER (
            Table4,
            Table4[Order] = EARLIER ( Table4[Order] )
                && Table4[Article number] = 999999
        ),
        Table4[Price]
    )
RETURN
    IF (
        Table4[delivery/store] = "delivery"
            && _OrderTotal < 350,
        IF ( _Charge, "yes", "Yes but forgot to add" ),
        "no"
    )

[Greg_Deckler]

@Anonymous So in the case of "Yes but forgot to add" should the measure add in the 15 Euro charge as part of it's calculation?

[Anonymous]

@Greg_Deckler that could be. But in the future the extra collom will be used to be a filter to see which employees did not charge the extra delivery cost. just to remember them that they need to add it to the order.

So when the dax formula is working, I would like to filter every week the orders that did not get the extra delivery cost because employees just forget to add it. 

If it is not clear at all just ask me!

Thanks.