Add column or replace value?

[JSData]

Hello, I'm having some trouble puting a formula together to add a column that will return Sub Account 12345 for the first row in the table below. The account number for the department that needs to be replaced will always be 236. 

Account	Sub Account	Invoice Number	Amount
236	00000	5555	-10
526	12345	5555	20
205	00000	5555	30

[v-xinruzhu-msft · Accepted solution]

Hi @JSData 

You can create a new column:

Column = var a=FILTER('Table',[Invoice Number]=EARLIER('Table'[Invoice Number]))
return IF([Amount]>0,[Sub Account],MAXX(a,[Sub Account]))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @JSData 

Not sure if i fully get you, try to create a column like this:

Result =

VAR _invoice = [Invoice Number]

RETURN

MAXX(

    FILTER(

         data, 

         data[Invoice Number]=_invoice

    ),

    data[Sub Account]

)

[JSData]

@Arul 

I have to apolgize I did not put enough detail in my orignal post. Your solution will work if it's only one replacement. The problem I'm working on is for each invoice number that has this account number I need to correct subaccount to be assigned to that account. I've put an expanded table with a desired result column that hopefully explains this more clearly. Again I apologize for being unclear the first time.

Account	Sub Account	Invoice Number	Amount	Desired result
236	00000	5555	-10	12345
526	12345	5555	20	12345
205	00000	5555	30	00000
236	00000	5554	-10	12343
526	12343	5554	20	12343
205	00000	5554	30	00000
526	12333	5253	20	12333

[Arul]

@JSData ,

try this formula in calculated column,

Sub account new =
VAR _value = 12345
VAR _result =
    IF ( 'Test Table'[Account] = 236, _value, 'Test Table'[Sub Account] )
RETURN
    _result

Thanks,

Arul