Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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.
This is done through QUERY Editor or Power Query
Right click the TABLE and select Edit Query
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.
Sales 2017 = CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Year]=2017))
Note: You'll need to replace 'Table1' with your real table name.
Regards
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |