Skip to Content
0
Former Member
Dec 03, 2009 at 09:37 AM

Percentage Variance calculation problem

184 Views

Hi experts,

I have report with following scenarios:

<h3>Account Name-- -Actual Budget ---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