Value adjustment

[jrpoli2000]

Can anyone please help me with 2 measures or calculated columns to make adjustments on INVOICE PAYMENT and PENDING columns. ADJUSTED INVOICE PAYMENT column will only calculate the rows with negative amounts on the PENDING column by adding INVOICE PAYMENT column and PENDING column while those with non-negative amount will reain as it is.

Below is the the data table and the expected output for the 2 measures.

DATA TABLE
INVOICE NO	INVOICE AMT	INVOICE PAYMENT	PENDING
1	35.07	36	-0.93
2	450.8	445.93	4.87
3	576	576.88	-0.88

Expected output :

MEASURE 1	MEASURE 2
ADJUSTED INVOICE PAYMENT	ADJUSTED PENDING
35.07	0
445.93	4.87
576	0

 Thanks!!!

[JDLee23 · Accepted solution]

Not an amazingly elegant solution, but this should work:

Add two calculated columns as below - 

Adjusted Invoice Payment = IF ( Pending < 0 , InvoicePayment - Pending, InvoicePayment )

Adjusted Pending = IF ( Pending < 0, 0, Pending )

Hope this helps!

[jrpoli2000]

Thanks a lot!!! You just cured my weeklong headache