Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
6 years ago
Solved

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 calc...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
harshagraj
Post Partisan
6 years ago

Hi Anonymous thank you so much for your help a big kudos to you. It worked perfectly.