To calculate Variance % from row level

[harshagraj]

Hi all, I have a screen shot below. I have a year field and Budget,Actual and Variance(Budget-Actual) and i have to calculate Variance % i,e Variance/Budget. Now i am getting wrong values as its calculating at row level and aggregating. Instead i need Subtotal(Vaiance)/Subtotal(Budget). Kindly help me to acheieve this.

Date	Budget_$_vw	Actual_$_vw	Variance_1$	Variance 1%
2012	309246.0928	315558.4	-6312.3072	-0.020411922
2012	300198.5664	274657.5087	25541.05766	0.085080545
2012	94325.76	112304.5005	-17978.74048	-0.190602657
2012	68124.16	83723.6585	-15599.4985	-0.228986288
2012	1765045.722	1762290.942	2754.779264	0.001560741
2012	425794.2272	429713.8499	-3919.622656	-0.009205439
2012	6971.904	10574.0544	-3602.1504	-0.516666667
2012	11756.544	4380.224	7376.32	0.627422481
	2981462.976	2993203.138	-11740.1623	-0.003937719
	Variance1% =Subtotal(Variance_1$)/Subtotal(Budget_$-vw)

[v-jayw-msft · Accepted solution]

Hi @harshagraj ,

Check the following measure about calculating Variance% per year.

Measure = CALCULATE(SUM('Table'[Variance_1$]),ALLEXCEPT('Table','Table'[Date]))/CALCULATE(SUM('Table'[Budget_$_vw]),ALLEXCEPT('Table','Table'[Date]))

 If you want calculate Variance% for all years, just use ALL() function instead of ALLEXCEPT() function.
Result would be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshagraj]

Hi @v-jayw-msft thank you so much for your help a big kudos to you. It worked perfectly.

[amitchandak]

@harshagraj 

Hope you are using sum(A)/Sum(B) not Sum(A/B)

Like

divide((sum(Actual_$_vw)-sum(Table[Budget_$_vw])),sum(Table[Budget_$_vw]))

[harshagraj]

Hi @amitchandak thank you but i am getting wrong values by that..I am getting -0.25 instead of -0.003.