Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help with something super simple

I am just learning, but want to create year over calculations.

 

Data looks just like this

Model           Year            Value

black              2017            5

blue                2016            8

 

My desired end state is this:

Model          2016                2017          Variance          Varaince %

Black             500                  200            -300                 -60&

 

I can make matrix and charts, but I want to want to create a measures that will allow me to calcuate the variance and varinace percent.

 

I can do a sum, and create a measure for total year sales, but I cant seem to figure out to make it so it ONLY sums if the year is 2016 or 2017.  In excel, i would use a sumif, but I cant see to find it here.  How do I essentially make a sumif cheat or is that the wrong way to go?

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

    For example in excel, it is a simple =SUMIF(B:B,"2017",D:D).  Give me the sum of column D, but only if Column b contains the data for 2017.

    The formula below to create a measure is for your reference. :smileyhappy:

    Sales 2017 = CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))

    Note: You'll need to replace 'Table1' with your real table name.

     

    Regards

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Anonymous

     

    Just Pivot your YEAR column using Value column as VALUES

    Then you can add the VARIANCE and VAR% columns easily

    You can unpivot back as well

     

     

    You will get

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I dont understand how that works.

      I am assuming I have to:

      create a measure that calculates the total sales for 2016,  I cant get it to only sum the sales of 2016.

      same thing for 2017. 

       

      The i can create the varaince.

      I dont understand how to do the steps above.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        This is done through QUERY Editor or Power Query

         

        Right click the TABLE and select Edit Query