cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

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

5 REPLIES 5
Highlighted
Super User I

@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

Regards,
Zubair

Highlighted
Frequent Visitor

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.

Highlighted
Super User I

@chrisbatson75

This is done through QUERY Editor or Power Query

Right click the TABLE and select Edit Query

Regards,
Zubair

Highlighted
Frequent Visitor

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.

Highlighted
Microsoft

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors