SSRS aggregate expression,get sum of column from values as expressed in each row

Question

I have to prepare a report that lists % of enquiry tunred into Sale. But we want the % to be = or < than 100%

=IIF(
Sum(Fields!Sold.Value)/SUM(Fields!Enquired.Value)>1,1,
IIF(Sum(Fields!Sold.Value)=0,"",
Sum(Fields!Sold.Value)/SUM(Fields!Enquired.Value))
)

and Sold value to be = or < enquired value.

=IIF(
Sum(Fields!Sold.Value) > SUM(Fields!Enquired.Value),
SUM(Fields! Enquired.Value),
IIF(Sum(Fields! Sold.Value)=0,"",Sum(Fields! Sold.Value))
)

So I have used expression included here to achieve that objective.

Issue is when I get the total it still shows the actual sum of Sold by which I mean, we would like the total to appear as 50 instead of 51
and % to appear as 96% instead of 98%.

Any suggestion if and how this can be done. Thanks. Am not sure how to title this so pls pardon if it isn't clear.

Answer 1

I think you're making life harder for yourself than is entirely necessary with those equations you have worked out currently.

First, if we re-think about your requirements they are that if Sold is greater than Enquired, use the Enquired value, otherwise the Sold value. This can be represented as

=iif(Fields!Sold.Value > Fields!Enquired.Value, 
    Fields!Enquired.Value,
    Fields!Sold.Value)  <-- This will be the GREEN <Expr> below

To then calculate the percentage for this product you should then use this calculation in the Sold/Enquired column as follows

=iif(Fields!Sold.Value > Fields!Enquired.Value, 
    Fields!Enquired.Value,
    Fields!Sold.Value)
 /Fields!Enquired.Value  <-- This will be the PURPLE <Expr> below

To determine the sum for the Sold items we need to determine the sum of either the Sold value, or the Enquired value, as follows

=Sum(iif(Fields!Sold.Value>Fields!Enquired.Value,
    Fields!Enquired.Value,
    Fields!Sold.Value))  <-- This will be the RED <Expr> below

Finally, you want to determine the percentage by dividing this value by the sum of the Enquired values, so the final expression needs to be

=Sum(iif(Fields!Sold.Value>Fields!Enquired.Value,
    Fields!Enquired.Value,
    Fields!Sold.Value))
/ sum(Fields!Enquired.Value)  <-- This will be the BLUE <Expr> below

This will give a design such as this

And when run will give this output

Hopefully that is the output you require. Please let me know if I can help further.