Forum Discussion
Solved
Match value in same table
Hello, I am trying to create a column that expresses whether something is a discount or not based on criteria within a table.
Below is the data set:
| BookingID | ExtraItemQty | ExtraItemExtendedCost |
| 25220696 | 12 | 900 |
| 25220883 | 7 | 525 |
| 25220692 | 4 | 300 |
| 25220876 | 24 | 1800 |
| 25220696 | 12 | -180 |
| 25220876 | 24 | -360 |
| 25220693 | 2 | 150 |
| 25220889 | 2 | 150 |
| 25220885 | 2 | 150 |
| 25220701 | 2 | 150 |
My desired output is as follows:
| BookingID | ExtraItemQty | ExtraItemExtendedCost | Type |
| 25220696 | 12 | 900 | Discount |
| 25220883 | 7 | 525 | Full Price |
| 25220692 | 4 | 300 | Full Price |
| 25220876 | 24 | 1800 | Discount |
| 25220696 | 12 | -180 | Discount |
| 25220876 | 24 | -360 | Discount |
| 25220693 | 2 | 150 | Full Price |
| 25220889 | 2 | 150 | Full Price |
| 25220885 | 2 | 150 | Full Price |
| 25220701 | 2 | 150 | Full Price |
So any ID with a negative ExtraItemExtendedCost would be considered a discount, and if there is not negative value for the ID at some point it is considered Full Price.
Thank you
New column =
var _cnt = countx(filter(Table, Table[BookingID] = earlier([BookingID]) && [ExtraItemExtendedCost] <0), [BookingID] )
return
If(isblank(_cnt) , "Full Price", "Discount")
1 Reply
New column =
var _cnt = countx(filter(Table, Table[BookingID] = earlier([BookingID]) && [ExtraItemExtendedCost] <0), [BookingID] )
return
If(isblank(_cnt) , "Full Price", "Discount")