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?