title: What other expressions can I use to try to calculate average daily amount in SSRS Report
body: <p>I am building a report with 2 matrix tables with different (but very similar) data sets. I need to calculate the average daily transaction amount where the user inputs the month and year they want to see. I was able to use the following expression on the first table with no issue.</p>
  <pre><code>=sum(fields!tot_rev.Value)/fields!Day.Value
  </code></pre>
  <p>Where &quot;Day&quot; is the day of the month. I used &quot;Day(trn_dt) as Day&quot; in my query to extract the day from the datetime string.</p>
  <p>I tried using the same expression in my second table but it only returns the sum total amount. If I use</p>
  <p><code>=sum(fields!amt.Value)/countdistinct(fields!Day.Value)</code></p>
  <p>it returns a value that seems to be random (I don't know how it came to that value with any calculation)</p>
  <p>The only way I have been able to successfully get the average was by inputting the number of days in the expression.</p>
  <pre><code>=sum(fields!amt.Value)/22
  </code></pre>
  <p>But I need this to work with the parameters and the number of days will not be constant.</p>
  <p>Here is a sample of my data from dataset1:</p>
  <pre><code>SELECT        MONTH(inv_dt) AS Month, DAY(inv_dt) AS Day, YEAR(inv_dt) AS Year, bill_by, inv_no, tot_rev
  FROM            inv_info
  WHERE        (status_cd IN ('FF', 'FP')) AND (MONTH(inv_dt) = @Month) AND (YEAR(inv_dt) = @Year)
   
  </code></pre>
  <p>Month, Day, Year, bill_by, inv_no, tot_rev</p>
  <p>10, 23, 2020, ERSA, 40444, 4881</p>
  <p>10, 23, 2020, ERSA, 40443, 2043</p>
  <p>10, 22, 2020, DYCO, 40435, 2504</p>
  <p>10, 22, 2020, ERSA, 40431, 20524</p>
  <p>Here is sample from dataset2 (table with the problem):</p>
  <pre><code>SELECT        YEAR(trn_info.trn_dt) AS YEAR, MONTH(trn_info.trn_dt) AS MONTH, DAY(trn_info.trn_dt) AS Day, trn_info.reg_no, trn_dtail.amt, trn_info.acc_by
  FROM            trn_info LEFT OUTER JOIN
                           trn_dtail ON trn_info.trn_no = trn_dtail.trn_no
  WHERE        (trn_info.status IN ('FP', 'FF')) AND (trn_info.tpe_cd IN ('AI', 'BI')) AND (trn_dtail.rte_cd = 'TT') AND (MONTH(trn_info.trn_dt) = @Month) AND (YEAR(trn_info.trn_dt) = @Year)
  </code></pre>
  <p>Year, Month, Day, reg_no, amt, acc_by</p>
  <p>2020, 10, 1, 40113, 377.96, JLMA</p>
  <p>2020, 10, 1, 40115, 6637.07, NASO</p>
  <p>2020, 10, 2, 40104, 790, MCGO</p>
  <p>2020, 10, 2, 40106, 1406.25, MCGO</p>
  <p>2020, 10, 3, 40100, 239.77, JEDX</p>
  <p>In the attached picture the upper left corner expression is: <code>=MonthName(Fields!MONTH.Value,false)</code></p>
  <p>The day of month is listed along the left with the total count (count of transactions) and sum for each processor(acc_by/bill_by) in the data field.</p>
  <p>I used <code>=iif(isnothing(Sum(Fields!amt.Value)), 0, sum(fields!amt.Value))</code> as the expression for the amt field.</p>
  <p>The column total is the total count and sum for that day from all processors.</p>
  <p>The row total at is the average daily count and average daily amt by processor and where the total column and total row intersect it is the average daily count for the month and I would like to show the average daily transaction amt for the month as well but this is what isn't working properly.</p>
  <p>See attached image:</p>
  <p><a href="https://i.stack.imgur.com/W0Dp6.png" rel="nofollow noreferrer">image of table 2</a></p>
  <p>Does anyone know what else I can try?</p>