Skip to main content

Hello all,

I am trying to create an Actual Gross Margin % in the Project Module. I tried the first part and it correctly calculates the Gross Margin $. And the second part correctly shows the Actual Revenue.

 

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

/

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

But when I put the divide sign in between it returns rubbish.

I am relatively new to GI and am struggling to understand where I am going wrong.

Any guidance would be most gratefully accepted.

Cheers,

 

Andrew

 

 

 

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

 


Reply