CODEWITHSUNDEEP
sql-serverreporting-services
lexirainbow
lexirainbow

Reputation: 41

Report Builder Sum IF according to groups

I want to add up all the 1's in the Debt Protection (Red) column, then divide by the sum of the Auto Loans and Personal Loans (blue column).

enter image description here

Right now I am using this formula:

=(sum(Fields!Debt_Protection.Value)) / sum(max(Fields!Loans.Value, "Loans"))

To get the sum of the Debt Protection column and divide it by the sum of the Loans group (since there is more than one group). I'm not sure how to get the sum of just the personal and auto loans instead of the whole Loans group. I'm thinking maybe I need to use a SUMIIF.

Here's what my report looks like now:

enter image description here

Upvotes: 0

Views: 717

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

In whichever part you need to perform the 'filter', multiply by 1 or 0 based on the criteria, so add something like 

=sum(iif(Fields!MyLoanTypeField.Value = "personal" or Fields!MyLoadTypeFields.Value = "auto", 1, 0) * Fields!MyFieldToSum.Value)

Upvotes: 1

Related Questions