Forum Discussion
Calculating a variance
Thanks for the update. I've tried that and so many other different ways, but the results are always unexpected and just wierd. For instance, I have this now. Why would the variance equal the issued sum for 2018?
hi cpunnett,
Please follow my formula. As mentioned in my previous post, assigning a measure to a variable will make it a constant.
- cpunnett8 years agoHelper II
I did follow your formula. Which is where [Issue 2017] and [Issue 2018] came from. I created those measures just from those specific years.
- danextian8 years agoSuper User
If you want to get the difference between 2018 and 2017 and since you have already created external measures, you could simply write Variance = [2018 Issued] - [2017 Issued]
If you placed the measure you just created in a pivot table and divide them into columns of years, this is what happens
- 2018 = [2018 Issued] - [2017 Issued] > This returns the value of [2018 issued] since this column is for 2018. What's visible in the current filter context is just 2018. 2017 is blank. So [2018 Issued] - BLANK() = [2018 Issued]
- 2017 = [2018 Issued] - [2017 Issued] > This returns a blank value for [2018 issued] since this column is for 2017 . What's visible in the current filter context is just 2017. So BLANK() - [2017 Issued] = negative of whatever the 2017 value is.
I think what you are trying to achieve is the year over year difference and not simply the difference between those two years. If so, this would have involved a Time Intelligence function if you had a standard data column but still a workaround is possible.
Here's the modified measure to be formatted as percentage
Variance YoY% =
VAR YEAR_ =
SELECTEDVALUE ( 'Table'[Year] )
VAR LY =
CALCULATE ( [Sum of Issued], ALL ( 'Table'[Year] ), 'Table'[Year] = YEAR_ - 1 )
RETURN
DIVIDE ( [Sum of Issued] - LY, LY )A similar tutorial can be found on youtube along with standard time intelligence calculations. https://www.youtube.com/watch?v=g_3eLaKgeEQ
- cpunnett8 years agoHelper II
Thanks danextian, that information got me further down the road. I can correctly calculate between 2018 and 2017, but I can't seem to figure out the previous years. I have first measure
2018 Issued3 = VAR Issue2018 = CALCULATE(SUM('Activity Counts3'[Issued]), 'Activity Counts3'[Year] = 2018) RETURN Issue2018Second measure
Issue Var Custom3 = VAR CurrentYear = SELECTEDVALUE('Activity Counts3'[Year]) VAR CurrentMonth = SELECTEDVALUE('Activity Counts3'[MonthNumber]) VAR CurrentProduct = SELECTEDVALUE('Activity Counts3'[Product]) RETURN CALCULATE([IssuedSum3], FILTER(ALL('Activity Counts3'), 'Activity Counts3'[Year] = CurrentYear - 1 && 'Activity Counts3'[MonthNumber] = CurrentMonth && 'Activity Counts3'[Product] = CurrentProduct))Then third
Issue3 Variance = VAR Issue2017 = [Issue Var Custom3] VAR Issue2018 = [2018 Issued3] VAR IssueSum = CALCULATE(Issue2018 - Issue2017) RETURN DIVIDE (Issue2018 - Issue2017, Issue2017, 0)This leads me to
How do I get the 2017 and 2016 calculations?