Forum Discussion

cpunnett's avatar
cpunnett
Icon for Helper II rankHelper 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
Icon for Super User rankSuper User
8 years ago

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's avatar
cpunnett
Icon for Helper II rankHelper II
8 years ago

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's avatar
    cpunnett
    Icon for Helper II rankHelper II
    8 years ago

    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's avatar
      cpunnett
      Icon for Helper II rankHelper II
      8 years ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        8 years ago

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