title: PowerBI - Average and Variance Calculation with conditions
body: <p>I am trying to calculate Variance and Average in PowerBI. I am running into Circular dependency errors.</p>
  
  <p>This is my Data,</p>
  
  <pre><code>Month Year  Item  Count
  1     2017  Chair  100
  1     2017  Chair  200
  1     2017  Chair  300
  1     2017  Bench  110
  1     2017  Bench  140
  1     2017  Bench  150
  2     2017  Chair  180
  2     2017  Chair  190
  2     2017  Chair  250 
  2     2017  Bench  270
  2     2017  Bench  370 
  3     2017  Chair  120
  3     2017  Chair  150  
  3     2017  Bench  180
  3     2017  Bench  190  
  4     2017  Chair  200
  4     2017  Chair  210
  4     2017  Bench  220
  4     2017  Bench  230
  .
  .
  .
  </code></pre>
  
  <p>Average = Sum of Counts for the Previous 3 months / 3</p>
  
  <p>Variance = (Average - Sum(CurrentMonth)) / Average</p>
  
  <p>So, because the average won't be meaningful for the first 3 months, I wouldn't be worried about that.</p>
  
  <p>Expected Output,</p>
  
  <pre><code>Month Year Item Sum(CurrentMonth) Average Variance
  1
  1
  2
  2
  3
  3
  4    2017  Chair     410               497      0.21
  4    2017  Bench     x                 y        z
  </code></pre>
  
  <blockquote>
    <p>Lets Say for Chair,</p>
    
    <p>Sum of Current Month = 200 + 210 = 410 </p>
    
    <p>Average of Last 3 Months = (100 + 200 + 300 + 180 + 190 + 250 + 120 + 150 )/ 3 = 1490 / 3 = 497 </p>
    
    <p>Variance = (497 - 410) / 410 = 87 / 410 = 0.21</p>
  </blockquote>
  
  <p>Kindly share your thoughts.</p>