Help with something super simple

[chrisbatson75]

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?

[Zubair_Muhammad]

@chrisbatson75

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

[chrisbatson75]

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]

@chrisbatson75

This is done through QUERY Editor or Power Query

Right click the TABLE and select Edit Query

[chrisbatson75]

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.

[v-ljerr-msft · Accepted solution]

Hi @chrisbatson75,

---

---

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. 

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

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

Regards