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

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

cpunnett's avatar
cpunnett
Icon for Helper II rankHelper II
8 years ago

How can I provide that?

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

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

    • cpunnett's avatar
      cpunnett
      Icon for Helper II rankHelper II
      8 years ago

      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 ResultWhat it should be
      MonthProductYearIssuedIssue VarianceIssue Variance
      1-JanRemodeler Excl. Exist Structure - Residential20182700.50
      1-JanRemodeler Excl. Exist Structure - Residential20171800.80
      1-JanRemodeler Excl. Exist Structure - Residential2016100-0.29
      1-JanRemodeler Excl. Exist Structure - Residential2015140 

       

      I appreciate your time

      • cpunnett's avatar
        cpunnett
        Icon for Helper II rankHelper II
        8 years ago

        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?