Search code examples
reporting-serviceshelpertabularcalculation

How can you use SSRS expression to calculate sum and average in tabular report

enter image description here

I want show the total for cost at the bottom should be

((112*1537)+(222*1567)+(54*2048))/388 = 1625

Currently I'm using the following expression but its giving me wrong avg

=(SUM(Fields!Units.Value))*(Fields!Rent.Value)/SUM(Fields!Units.Value)
Solution

  • The expression is multiplying the total Units by a single Rent value.

    (112 + 222 + 54) * (1537 or 1567 or 2048) / (112 + 222 + 54)

    It should have a SUM around the Rent, but it would still be incorrect.

    (112 + 222 + 54) * (1537 + 1567 + 2048) / (112 + 222 + 54)

    It should sum the total Costs (Units * Rent) and then divide by the number of units for the average:

    =SUM(Fields!Units.Value * Fields!Rent.Value) / SUM(Fields!Units.Value)

    Which would end up as:

    (112 * 1537 + 222 * 1567 + 54 * 2048) / (112 + 222 + 54)