SSRS Ignore some data in column group totals

Question

I have a report that uses column groups and has a total column. there are 2 fields Numerator and denominator. In the group section I calculate the expression =IIF(sum(Fields!denominator.Value=0,"N/A",Sum(Fields!Numerator.Value)/sum(Fields!denominator.Value)) and I use the same expression in the total column is there a way to ignore the groups that would get the N/A

if the data looks like this

group , numerator ,denominator A,3,8 A,0,2 A,2,0 B,1,0 b,1,0

A gets .5 B gets N/A Total gets .7 would like it to get .5

thanks for your help

Answer 1

You are basically trying to total subtotals. There's no easy way to reference subtotals in SSRS aggregate expressions. So instead I would recommend adding another column to your dataset that is the total denominator for the group. In other words, do this calculation in the SQL.

So your first row of data would include A, 3, 8, 10 (the total denominator for group A).

Then in your total expression you would check this column instead of the denominator.

This would give you 5 and 10 as your total numerator and denominator.