Grand Total value doesn't match with Top N Filtered values in SSRS

Question

I have a report in reporting services. In this report, I am displaying the Top N values. But my Grand Total is displaying the sum of all the values.

Right now I am getting something like this.Here N = 2

 +-------+------+-------------+
| Area   |ID   |  Count      |
+-------+------+-------------+
| - A   |      |   4         | 
|       |  a1  |   1         | 
|       |  b1  |   1         | 
|       |  c1  |   1         | 
|       |  d1  |   1         | 
|       |      |             |
| - B   |      |   3         | 
|       |  a2  |   1         | 
|       |  b2  |   1         | 
|       |  c2  |   1         |  
|       |      |             | 
|Grand  |      |  10         | 
|Total  |      |             | 
+-------+------+-------------+

The correct Grand Total should be 7 instead of 10. A and B are toggle items(You can expand and contract)

How can I display the correct Grand Total using Top N filter?

I also want to use the filter in the report and not in the SQL query.

Answer 1

You should use the filter on the Dataset. Filtering the report object itself only turns off the items (rows, for example) visibility. The item / row itself will still be part of the group and will be used for calculations.

Answer 2

I found a way to solve my question. As Ido said I worked on the dataset. I am using Analysis Cube. So in this cube I created a Named Set Calculation.

In this set I used the TopCount() function. It filters out the TOP N values where N can be integer according to your choice.

So the final Named Set in this case is :-

TopCount([Dim Area].[Area].[Area], 2, ([Measures].[Count]))

This will give you Grand total of Top N filtered values.