I am trying to to use data in one column in a second column, but running into a circular arguement. Is there any way to avoid this. My table looks like this
<tbody>
</tbody>
I am trying to calculate the last column from the 5th column. The lines 10000 are all part of the service LUBE01, but the types are for parts (items) and the sale cost (service code). I need to group these together for other calculations.
The code I tried is this
I thought the IF clause would exclude any rows where the circular argument would happen. Is there a way to write this please?
PINVNo | Customer No | Service Code Line | Type | Code | Service |
PINV000002 | 2000003 | 10000 | Item | 501720169 | LUBE01 |
PINV000002 | 2000003 | 10000 | Service Code | LUBE01 | LUBE01 |
PINV000002 | 2000003 | 10000 | Item | ENOCAS012B1 | LUBE01 |
PINV000002 | 2000003 | 20000 | Item | MOTSLOT | MOT01 |
PINV000002 | 2000003 | 20000 | Service Code | MOT01 | MOT01 |
PINV000002 | 2000003 | 30000 | Service Code | SUP01 | SUP01 |
<tbody>
</tbody>
I am trying to calculate the last column from the 5th column. The lines 10000 are all part of the service LUBE01, but the types are for parts (items) and the sale cost (service code). I need to group these together for other calculations.
The code I tried is this
Code:
=IF( [Type]="Service code",
[Type],
CALCULATE(
VALUES('Posted Service Invoice Line'[code]),
FILTER('Posted Service Invoice Line',
[PINVNo]=EARLIER([PINVNo]) &&
[Service Code Line]=EARLIER([Service Code Line]) &&
[Type]="Service Code"
)
)
)