calculated column based on values in other row

[Anonymous]

I got a sales table with different types of sales.

I got a “reservation” and the “actual” sales.

Table looks like this:

Customer, OrderID, Sales, Sales Type, Item

A, 123, 100, “Reservation”, D

A, 123, 25, “Sales”, D

A, 234, 43, “Sales”, D

Customer can make a reservation amount which they can buy.

Sales can be a part of a reservation, but can also be a non related sale.

The relation is in the OrderID. If there is a “Reservation” with the same orderID as sales, then the sales is part of the “reservation”.

I want to mark the second row as part of a “Reservation”. (calculated column)

I want to know how much of a reservation is left (In this case 100-25 = 75)

Your help is much appreciated

[Nathaniel_C · Accepted solution]

Hi @Anonymous 

I created two columns, but you can do it in one. Sometimes easier to understand in two and debug.

So, logic just get the value and if it is sales, make it negative.

Then sum all based on customer and order id. See table and visual.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

If pos/neg = var Reservation = IF(ORD[Sales Type] = "Reservation",ORD[Sales],ORD[Sales]*-1)


return Reservation

Left over = CALCULATE(SUM(ORD[If pos/neg]),FILTER(ALLEXCEPT(ORD,ORD[Customer],ORD[Order Id]),max(ORD[If pos/neg])))

[Anonymous]

Thanks so much. This got me on the rigth track. 

With some small adjusments i got it to work.