Sum(First(…)) not allowed limitation in SSRS

I have been struggling with an issue in SSRS for some time now and finally solved the issue:

The Issue

I have this data:

A query result with columns ROWGROUP, AREA, ColumnName, Value and guid: group A has AREA 100 on four rows (COL1 to COL4), group B has AREA 180 on three rows, group C has AREA 110 on two rows

In the report I want the ROWS and COLUMNS to be dynamic, adding a matrix table to the report and setting the ROWGROUPS to the rows and ColumnName to the Columns and Value to the Details:

The SSRS Report Wizard’s Design the Matrix step, with ColumnName in Columns, ROWGROUP in Rows and Value in Details

When running the report it displays correctly:

Report1: a matrix with rows A, B and C and columns COL1 to COL4

Now I want to add the AREA to show the ROWGROUPs fictional areas, I only want to show the AREA for each ROWGROUP and not the sum :

So not The AREA cell set to [Sum(AREA)] but The AREA cell set to [AREA]

This displays correctly:

Report1 with an AREA column: A 100, B 180, C 110

Now I want to add a Total at the bottom, the issue here is that ssrs sums the AREAs like such:

Report1 with a Total row whose AREA total is 1160, highlighted, because every row’s AREA was added up

The Solution

One way of getting the correct total of the AREA column is to add 2 new columns to our sql query:

SELECT Test.*
       ,ROW_NUMBER() OVER (ORDER BY ROWGROUP) AS  'RowNumber'
       ,RANK() OVER (ORDER BY ROWGROUP) AS  'Rank'
FROM   Test

and using an expression on the AreaTotal:

=Sum(IIf(Fields!Rank.Value = Fields!RowNumber.Value, Fields!AREA.Value, 0))

Producing the desired result:

Report1 with a Total row whose AREA total is 390: 100 + 180 + 110