Calculating a variance

[cpunnett]

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 with keeping the submission with the state and product.  I think I need a FILTER(), but not sure how that would work.

The SQL would look like:

SELECT (a.Submission - b.Submission) / b.Submission

FROM dbo.Table a

JOIN dbo.Table b ON a.StateCode = b.StateCode

AND a.ShortProductDesc = b.ShortProductDesc

AND a.SubmissionYearMonth = b.PriorYearsSubmissionYearMonth

[danextian]

Hi @cpunnett,

You can try creating a measure in DAX using the following formula

VARIANCE =
VAR SUBMISSIONS_ =
    SUM ( 'Table'[Submission Column] )
VAR SUBS2017_ =
    CALCULATE ( SUBMISSIONS_, 'Table'[Year Column] = 2017 )
VAR SUBS2018_ =
    CALCULATE ( SUBMISSIONS_, 'Table'[Year Column] = 2018 )
RETURN
    DIVIDE ( SUBS2018_ - SUBS2017_, SUBS2017_ )

[cpunnett]

That helps me understand how it would work, I appreciate that.  But I can't seem to get mine to work.  All I get are zeroes.  When I return either variable I get the same numbers as the sums.  I not sure what I'm not doing correctly yet.

[cpunnett]

Can anyone tell me why I'm seeing the error "The expression refers to muliple columns.  Multiple columns cannot be converted to a scalar value."  From this DAX?

Issue Variance = VAR Issued = SUM('Activity Counts6'[Issued])
    VAR SUBS2017 = CALCULATE('Activity Counts6', 'Activity Counts6'[Year] = 2017)
    VAR SUBS2018 = CALCULATE('Activity Counts6', 'Activity Counts6'[Year] = 2018)
    RETURN DIVIDE (SUBS2018 - SUBS2017, SUBS2017)

[cpunnett]

I was using a table name where I should've used the variable....

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

[danextian]

Please provide a sample importable data, not an image. I would like to test the formula.

[cpunnett]

How can I provide that?

[danextian]

You may paste a data table here or post a link to your sample data.

[cpunnett]

This is my query

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

Here is sample data with current and expected results

				DAX Result	What it should be
Month	Product	Year	Issued	Issue Variance	Issue Variance
1-Jan	Remodeler Excl. Exist Structure - Residential	2018	27	0	0.50
1-Jan	Remodeler Excl. Exist Structure - Residential	2017	18	0	0.80
1-Jan	Remodeler Excl. Exist Structure - Residential	2016	10	0	-0.29
1-Jan	Remodeler Excl. Exist Structure - Residential	2015	14	0

I appreciate your time

[cpunnett]

I've figured out that the variables Issue2017 and Issue2018 are equal, which is why I'm getting zeroes.  But I can't seem to get correct.  Any Ideas?

[danextian]

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]

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]

hi @cpunnett,

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

[cpunnett]

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]

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]

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?