Percentage Variance calculation problem

[Former Member]

Hi experts,

I have report with following scenarios:

<h3>Account Name---ActualBudget---Variance --Variance Percentage </h3>

Account Level 1______3400_______5200____-1800______________X

-Account Level 2______2200_______4200_____-2000______________XX

--Account Level 3______1000_______2000_____-1000_______________-50

--Account Level 3______1200_______2200_____-1000_______________-45.45454545

-Account Level 2______1200_______1000_____200________________XX

This is how the report is now displayed. I have done a Hierarchical Grouping of database field (Field Account). Here the Actual and Budget columsn are the summary fields with "sum" and the Summarize across hierarchy checked.. The formula for Variance Percentage is

 if Budget =0 then 0 
else  ((Variance/Budget)*100) 

Now, all the output is displayed in Group Header. The problem here is the calculation of Variance Percentage of Levels above Level3 i.e. Level 2 and Level 1. Level 2 percentage should be the calculation of (Variance / Budget ) *100 of that Level only i.e. Level2 and same should be the case for Level 1. When I apply the summary field on this column, the percentage calculation is all wrong as the Level 2 will be sum of Level 2 variance percentages which is not correct output.

I need a solution to this quickly.. if some more clarification is required please reply to this post..

Thanks

[Former Member · Accepted solution]

Try to create a variable and initialize with 0 and assign the calculated value in it based on the condition given. Then reset this variable everytime.

[Former Member · Accepted solution]

Thank you very much ananth2009 for your quick reply.

Can you please explain me with examples on where should i create the global variable and others. It would be a great help.

[Former Member · Accepted solution]

Can you please give me your expected output?

[Former Member · Accepted solution]

Here is my expected output,

<h3>Account Name---ActualBudget---Variance --Variance Percentage</h3>

Account Level 1______3400_______5200____-1800______________-34.61538462

-Account Level 2______2200_______4200_____-2000______________-47.61904762

--Account Level 3______1000_______2000_____-1000_______________-50

--Account Level 3______1200_______2200_____-1000_______________-45.45454545

-Account Level 2______1200_______1000_____200________________20

Note: The Actual and Budget columns are summary fields.

[Former Member · Accepted solution]

Don't use a summarized field. Instead create a formula field

Right Click on the formula field which will be there in your Field Explorer --> Select New --> Provide the name for your formula field in the text box shown

and put the condition there like

if {test.Budget} =0 then 0 
else  (({test.Variance}/{test.Budget})*100) 

Once formula field is created place in the desired location where you need to show the Variance Percentage.

Hope this helps!

[Former Member · Accepted solution]

Still facing any problem?