% down from value in other column

[AlexGavri]

Hello everyone,

I looking for a way to filter out some jobs done that have been first quoted a certain value but at the end the ended up paying a amount that is a bit lower (maybe set a percentage from first value).

Thank you

[danilang]

Hi @AlexGavri 

Try something like this in a Filter tool

[Bill.Req Gross Value]>=0.95*[Amount Quoted]

Dan 

[jdminton · Accepted solution]

First, I would point out that it appears that some records may exist as duplicates in the dataset. You might want to do something to filter those duplicates out first. Hopefully you have some kind of unique identifier elsewhere in your data.

For the formula, I would suggest something slightly different than danilang. I would create a separate column so that you can have a formula to calculate the percent first. This will also allow you to adjust for those zero amounts that likely have no billings yet. In other words:

IF [Bill.Req Gross Value]=0 OR IsEmpty([Bill.Req Gross Value])

THEN Null()

ELSE [Bill.Req Gross Value]/[Amount Quoted]

ENDIF

After you get the result for this, you can sort by percent, filter by percentage, or any other number of things.