Forum Discussion
To calculate Variance % from row level
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) | ||||
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.
4 Replies
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]))
Hi amitchandak thank you but i am getting wrong values by that..I am getting -0.25 instead of -0.003.
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.
Hi Anonymous thank you so much for your help a big kudos to you. It worked perfectly.
