cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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. Smiley Happy

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

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

 

Regards

View solution in original post

5 REPLIES 5
Highlighted
Super User I
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

 

9003.png

 

You will get

 

9004.png

Regards,
Zubair


Highlighted

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

@chrisbatson75

 

This is done through QUERY Editor or Power Query

 

Right click the TABLE and select Edit Query

 

 

Regards,
Zubair


Highlighted

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

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. Smiley Happy

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

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

 

Regards

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors