Forum Discussion
Value adjustment
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!!!
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!
2 Replies
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!
Thanks a lot!!! You just cured my weeklong headache:smileyhappy:
