title: SSRS Total based on Expression Value
body: <p>I'm trying to create a total that is based on a calculation to provide Gross Weight.  Now before someone suggests (modify the SQL to give results and then total that in SSRS) this is coming from a dynamics AX query which i don't have access to.  This is my report </p>
  
  <p><img src="https://i.stack.imgur.com/OjTfy.png" alt="enter image description here"></p>
  
  <p>Which gives results </p>
  
  <p><img src="https://i.stack.imgur.com/DrmX8.png" alt="enter image description here"></p>
  
  <p>Total highlighted in red is wrong (it should be 18043.25 if you add up the lines).  Which i've done by right clicking on gross weight expression and gone 'Add Total'.</p>
  
  <p>Expression for Gross Weight (at line level)</p>
  
  <pre><code>=SUM(Fields!qty.Value * (Fields!NetWeight.Value + Fields!TaraWeight.Value)) + IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value)
  </code></pre>
  
  <p>The Sum bit is pretty straightforward just summing netweight and taraweight but then needs to add the pallet weight which varies depending on whether it's a pallecon or not.  The total seems to be adding the <code>IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value</code></p>
  
  <p>part only once instead of evaluating for each line and summing.  I tried wrapping the expression in another SUM however i got around 23300 for gross weight which is way too high.  As I stated before I should be getting 18043.25.  How do you do a total based on a total expression of another column? Thanks!</p>
  
  <p><strong>* UPDATE *</strong> Total Expression Testing</p>
  
  <pre><code>(test1) =SUM(Fields!qty.Value * (Fields!NetWeight.Value + Fields!TaraWeight.Value)) + RunningValue(IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value), Sum, "Group1") = 23323.22
  
  (test2) =RunningValue(SUM(Fields!qty.Value * (Fields!NetWeight.Value + Fields!TaraWeight.Value)) + IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value), SUM, "Group1") = 3267881.87
  
  (test3) =RunningValue(Fields!qty.Value * (Fields!NetWeight.Value + Fields!TaraWeight.Value) + IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value), SUM, "Group1") = 23323.22
  </code></pre>
  
  <p>After much testing, I believe I have an issue somewhere on grouping... I think this should give me what i'm after as a total expression.  Please correct me if i'm wrong.</p>
  
  <pre><code>=SUM(Fields!qty.Value * (Fields!NetWeight.Value + Fields!TaraWeight.Value)) + RunningValue(IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value), SUM, "Group1") = 23323.21805
  </code></pre>
  
  <p>Which is too high.  I ran the expression on it's own which gave me about 33 per line total of 1056 so I have no idea where the extra is coming from...</p>
  
  <p><strong>* FURTHER UPDATE *</strong></p>
  
  <p>I think I've found the issue it's to do with this expression : </p>
  
  <pre><code>IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, (Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value))
  </code></pre>
  
  <p>This gives 33kg per line. </p>
  
  <p>However <code>SUM(IIF(Fields!palletTypeId.Value = "Pallecon", Fields!PalleconWeightKg.Value, (Fields!BluePalletWeightKg.Value + Fields!PackagingWeightKg.Value)))</code></p>
  
  <p>in the total field gives 6336.  I should be getting 33 * 32 lines = 1056.</p>
  
  <p>Where is the extra 5000 coming from?!?!?!?</p>