Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chrisbatson75
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

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
Zubair_Muhammad
Community Champion
Community Champion

@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

Please try my custom visuals

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.

@chrisbatson75

 

This is done through QUERY Editor or Power Query

 

Right click the TABLE and select Edit Query

 

 


Regards
Zubair

Please try my custom visuals

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.