Hi ​@ajmooney,

Please try below formula.

=iif(fPMBudget.Type] = 'I','PMBudget.CuryActualAmount],0) - iif(fPMBudget.Type] = 'E','PMBudget.CuryActualAmount],0)
/ nullif(iif(fPMBudget.Type] = 'I','PMBudget.CuryActualAmount],0), 0)

Add Any amount field as Schema Field as well in the Results Grid tab, so it will show the Amount up to 2 decimal values.

Hope, it helps

You might also want to nest nullif or IsNull on the numerator as well. 

Only tweak I would make to ​@Dipak Nilkanth ‘s solution is below. If theres a null in the denominator its probably also in the numerator.

= (nullif(iif(fPMBudget.Type] = 'I',  PMBudget.CuryActualAmount], 0), 0) 
   - nullif(iif(fPMBudget.Type] = 'E',  PMBudget.CuryActualAmount], 0), 0)) 
  / nullif(iif(fPMBudget.Type] = 'I',  PMBudget.CuryActualAmount], 0), 0)

Hi Dipak,

I tried your suggestion and also changed the Schema Field to PMBudget.Amount and tried the Aggragate Function with Sum and also blank.

The GI returns the Actual Revenue $ rather than the Gross Margin %.

Any other ideas?

Hi Matt,

I also tried your suggestion and this returned blank cells.

Any other ideas?

You need to convert the amount you're dividing by into a decimal by using CDec():

=(iif([PMBudget.Type] = 'I',[PMBudget.CuryActualAmount],0) - iif([PMBudget.Type] = 'E',[PMBudget.CuryActualAmount],0))

/

CDec(iif([PMBudget.Type] = 'I',[PMBudget.CuryActualAmount],0))

Sorry Daryl,

This does not seem have worked - as you can see from the attachment the GI seems to be returning whole numbers of 1 or 2.

Do you have any other ideas?

Cheers,

Andrew

I’ll give this another try on Monday. However, until then, I suggest using ChatGPT or Anthropic to troubleshoot your formula. 
 

just keep giving it the results of the formula until its correct.

be specific about the tables, fields, and aspects you change in the results grid.

Hi Matt,

Brilliant suggestion though I used Perplexity instead of ChatGPT. Had to make a couple of syntax changes but it worked - thanks to everybody that contributed. And here is the formula:

= (sum(iif((PMBudget.Type] = 'I', PMBudget.CuryActualAmount], 0) - iif((PMBudget.Type] = 'E', PMBudget.CuryActualAmount], 0))) / nullif(sum(iif((PMBudget.Type] = 'I', PMBudget.CuryActualAmount], 0)),0)*100

Cheers,

Andrew