What is wrong with this formula in GI?

Hi ​@ajmooney,

Please try below formula.

=iif([PMBudget.Type] = 'I',[PMBudget.CuryActualAmount],0) - iif([PMBudget.Type] = 'E',[PMBudget.CuryActualAmount],0)
/ nullif(iif([PMBudget.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([PMBudget.Type] = 'I', [PMBudget.CuryActualAmount], 0), 0) 
   - nullif(iif([PMBudget.Type] = 'E', [PMBudget.CuryActualAmount], 0), 0)) 
  / nullif(iif([PMBudget.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

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