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    ...
  • 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

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        That is not helpful at all.  Can anyone please show me how to write a dax forumal that sums up a column, but only if another column contains a certain data point.  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.