Avoid circular conflict

masplin

Active Member
Joined
May 10, 2010
Messages
406
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

PINVNoCustomer NoService Code LineTypeCodeService
PINV000002200000310000Item501720169LUBE01
PINV000002200000310000Service CodeLUBE01LUBE01
PINV000002200000310000ItemENOCAS012B1LUBE01
PINV000002200000320000ItemMOTSLOTMOT01
PINV000002200000320000Service CodeMOT01MOT01
PINV000002200000330000Service CodeSUP01SUP01

<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"
                                         )
                            )   
      )
I thought the IF clause would exclude any rows where the circular argument would happen. Is there a way to write this please?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,096,296
Messages
5,449,510
Members
405,569
Latest member
deanro

This Week's Hot Topics

Top