Skip to main content
C
CrispinC
Newcomer
March 1, 2022
Solved

Calculate variance percentages for different rows

  • March 1, 2022
  • 2 replies
  • 0 views

Hi

I'm struggling to add lines to show Gross Margin % ((Total income less product related costs)/Total income)*100), overhead as a percentage of total income etc.

I've tried using Getdatacell referencing the rows but clearly am doing something incorrectly since I get red cells. The formula I have used is GetDataCell(((CVR(Rev_Total)-CVR(RevRelCosts_Total))/CVR(Rev_Total))*100):Name(Gross Margin) where Rev_Total is A#[Total Income] and RevRelCosts_Total is A#[Total Revenue Related Costs].

I'd be grateful for any advice.

Thanks

Best answer by PeterFu

Hi,

 

Maybe it's better to create a Gross Margin account and set it up with DynamicCalc as Account Type. Then use a member formula to calculate the Gross Margin, 'Calculate the Percentage
Return api.Data.GetDataCell("Divide(A#61000:O#Top,A#60999:O#Top) * 100") - This is from Golfstream application.

If you use the Admin Tool to create the formula, you could easily include the formula to do the Drill-down on the accounts involved in the calculation. I would say that is a better way of doing it.

 

Peter

 

 

2 replies

P
PeterFuAnswer
Newcomer
March 1, 2022

Hi,

 

Maybe it's better to create a Gross Margin account and set it up with DynamicCalc as Account Type. Then use a member formula to calculate the Gross Margin, 'Calculate the Percentage
Return api.Data.GetDataCell("Divide(A#61000:O#Top,A#60999:O#Top) * 100") - This is from Golfstream application.

If you use the Admin Tool to create the formula, you could easily include the formula to do the Drill-down on the accounts involved in the calculation. I would say that is a better way of doing it.

 

Peter

 

 

    C
    CrispinCAuthor
    Newcomer
    April 7, 2022

    Thanks Peter

    aricgresko
    aricgresko
    Veteran
    March 1, 2022

    DynamicCalc type account would be the best way.  This also helps to eliminate the potential need of a column or row override depending how the rest of the report looks.  Another added benefit is end users could pull that account in a Quick View or XFGetCell without doing excel math.

    Terms & ConditionsAccessibility statement