Resolution for issue of wrong totals in tables grouped with filters in SSRS

Previous post I explained the fruit you can have by using filters when grouping table columns. Still, it can be frustrating if you need subtotals for any numeric values in table. What really happens is totals ignore the filters. (Just behave like there is no filter!)

Suppose we add group by some field, but only needs values for given two countries. (i.e. France and Belgium). Filter will be seen as below;

So we also need to get sub totals for groups (suppose, field name as revenueVal). So what we usually do is put below method in the total row;

SUM(revenueVal)

As I explained, issue is this will sum up all the revenues of all the countries not just for filtered ones.

This is the resolution.
Define another calculated field in the DataSet.

And add the same filter criteria as an expression;

Expression will be looked like this… hope you understand the simple logic. We are replacing zero for all the rows which do not meet the criteria.

* Note: If you are going to use the totals in Matrix (instead of a table) please use “nothing” instead of “0” in above expression.

Now we are using our own calculated field for calculation; 

SUM(revenueVal_Filtered)

Now this subtotal adheres our criteria.