Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago
Solved

Variance and variance %

Please advise on how to create a variance and a variance % as per below: I have created two calculations which compute a measure for a period of time. This year to date and last year to date:   1....
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Variance = [This YTD] - [Last YTD]

Variance% = ([This YTD] - [Last YTD]) / [Last YTD]

 

  • MarcUrdang's avatar
    MarcUrdang
    Post Patron
    6 years ago

    Hi Greg .. I see you already posted back on this ... I originally tried this .... but get the same number for each as the example shows below:

     

    hank you so much for the reply .. on the variance amount (this year - last year) .. the value doesn't reflect correctly. The variance amount is the same as the actual amount.. it shows as follows:

     

    Country        2019     var          2020    var

    Spain            100      100          150     150

     

    it shoud show

     

    Country        2019     var          2020    var

    Spain            100      0             150      50

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Well, those are your measures MarcUrdang ! 🙂

       

      It is difficult to say, but you are using time "intelligence" so that's a crap shoot in my opinion. You are using them a little weird, you might try SAMEPERIODLASTYEAR. I assume you have a separate Date table.

       

      You could see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for. https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

       

      If all that fails sample data, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

      The most important parts are:
      1. Sample data as text, use the table tool in the editing bar
      2. Expected output from sample data
      3. Explanation in words of how to get from 1. to 2.

      • MarcUrdang's avatar
        MarcUrdang
        Post Patron
        6 years ago

        I was originally using the itelligence option (quick measures) which actually worked .. but changed the calcs manually shown in my original post ... it might be 'weird' but as a fairly new user I'm just trying to find my way.

         

        Will try your idea and await other replies which might also help show me the way.

         

        thanks for your help thus far

        Marc