title: How to calculate an Average Value in SSRS Chart
body: <p>I am building a report in Report Builder for SSRS. My Data looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/VoytY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VoytY.png" alt="Raw Data"></a></p>
  
  <p>My Column Chart is grouped by Organization, with a series group of Category and each of the 6 categories will apply to each Organization. </p>
  
  <p>I'd like to add an additional Line Series to show an overall average of all categories for each Organization.</p>
  
  <p>Can anyone suggest the way to achieve this? Do I need a new calculated field in SSRS, should I enhance my SQL query to get the calculation done there?</p>
  
  <p>My SQL Query is:</p>
  
  <pre><code>select OrganizationUnitID,Category,AVG(NumericValue) as average
  , case 
   when AVG(NumericValue) &lt;= 1 then 0
   when AVG(NumericValue) &gt; 1 and AVG(NumericValue) &lt;= 2 then 50
   when AVG(NumericValue) &gt; 2 and AVG(NumericValue) &lt;= 3 then 75
   when AVG(NumericValue) &gt; 3 then 100 end
   as percentage
  from SurveyMetricView
  group by OrganizationUnitID,Category
  order by OrganizationUnitID
  </code></pre>
  
  <p>The desired result is this chart:</p>
  
  <p><a href="https://i.stack.imgur.com/5VTwq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5VTwq.png" alt="End Result Chart"></a></p>
  
  <p>Thanks in advance for any advice.</p>