Forum Discussion

cpunnett's avatar
cpunnett
Helper II
8 years ago

Calculating a variance

I'm trying calculate a variance and need help please.  I'm trying to take submissions from Year 2018 subtract submissions from 2017, then divide that number by submissions from 2017.  My problem is w...
Need Help
danextian's avatar
danextian
Super User
8 years ago

My mistake. I didn't realize that assigning an aggregation to a variable is equivalent to the aggregation not getting filtered at all. What happens is VAR Issued will always be the total sum of all issued and will never be filterd by CALCULATE().

 

Once the variable has been assigned a value, that value cannot change during the execution of the RETURN portion of the formula. From that perspective, the variables act more like constants than regular variables in a traditional programming language context. https://exceleratorbi.com.au/using-variables-dax/

 

So we have to split the formula into two.

 

Sum of Issued =
SUM ( SubmissionActivity[Issued] )
Issue Variance = 
VAR Issue2017 =
    CALCULATE ( [Sum of Issued], 'SubmissionActivity'[Year] = 2017 )
VAR Issue2018 =
    CALCULATE ( [Sum of Issued], 'SubmissionActivity'[Year] = 2018 )
RETURN
    DIVIDE ( Issue2018 - Issue2017, Issue2017 )

 

 

cpunnett's avatar
cpunnett
Helper II
8 years ago

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?

  • danextian's avatar
    danextian
    Super User
    8 years ago

    hi cpunnett,

     

    Please follow my formula. As mentioned in my previous post, assigning a measure to a variable will make it a constant. 

  • cpunnett's avatar
    cpunnett
    Helper II
    8 years ago

    I did follow your formula.  Which is where [Issue 2017] and [Issue 2018] came from.  I created those measures just from those specific years.

  • danextian's avatar
    danextian
    Super User
    8 years ago

    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

     

  • cpunnett's avatar
    cpunnett
    Helper II
    8 years ago

    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 Issue2018

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