Calculate the variance percentage between two fields in a project report?

[ManojPe]

I am trying to calculate the variance percentage between two fields in a project report"defaultBaseline:resourcePlannerBudgetedLaborCost" and "resourcePlannerBudgetedLaborCost"

Test code used:

displayname=Budget Percentage Variance

linkedname=defaultBaseline

namekey=view.relatedcolumn

namekeyargkey.0=defaultBaseline

namekeyargkey.1=resourcePlannerBudgetedLaborCost

querysort=defaultBaseline:resourcePlannerBudgetedLaborCost

textmode=true

valueexpression=ROUND(PROD(DIV(SUB({defaultBaseline:resourcePlannerBudgetedLaborCost},{resourcePlannerBudgetedLaborCost}),{defaultBaseline:resourcePlannerBudgetedLaborCost}),100),0)

valueformat=currencyStringCurrencyRounded

The column shows no results. Any help from the community.

Thanks

[Richard_Le_]

Hi Manoj,

There was an error in your syntax within the value expression. Instead of {defaultBaseline:resourcePlannerBudgetedLaborCost}, you need to set it as {defaultBaseline}.{resourcePlannerBudgetedLaborCost}.

The below text mode code should do it:

displayname=Budget Percentage Variance

linkedname=defaultBaseline

namekey=view.relatedcolumn

namekeyargkey.0=defaultBaseline

namekeyargkey.1=resourcePlannerBudgetedLaborCost

querysort=defaultBaseline:resourcePlannerBudgetedLaborCost

textmode=true

valueexpression=CONCAT(ROUND(PROD(DIV(SUB({defaultBaseline}.{resourcePlannerBudgetedLaborCost},{resourcePlannerBudgetedLaborCost}),{defaultBaseline}.{resourcePlannerBudgetedLaborCost}),100),0),'%')

valueformat=HTML

I've also updated the value expression in the code above to concatenate a percentage symbol to the value since you are trying to surface a percentage in your report. Value format has also been changed to HTML instead of currency, since you wont want a currency symbol in your percentage.

Hope this helps!

Best Regards,

Rich.

[Kundanism]

Hi @Manoj Peruri‚ @Manoj Peruri‚ @Richard Leek‚

Thanks for sharing this one of the field, could you please share the formula what you are following for this above text mode?

Thanks & regards,

kundan.

[Kundanism]

Hi @Manoj Peruri‚ @Richard Leek‚

Thanks for sharing this one of the field, could you please share the formula what you are following for this above text mode?

Thanks & regards,

kundan.

[Richard_Le_]

Hi Kundan,

What do you mean by formula? In my reply to Manoj is the full set of text mode including the value expression?

Best Regards,

Rich.

[Kundanism]

Hi @Richard Leek‚

The general formula for calculating variance percentage not in text mode. This is to understand what difference brings the value of Budget Variance Percentage.

Something like this.

Mvh

Kundan