How to combine aggregates within a group with aggregates across groups within SSRS

Question

With this dataset:

Category | Amount
A        | 5
A        | 3
B        | 6
B        | 2
B        | 1
C        | 7

I want to create a tablix grouping on category, displaying the percentage of the total amount:

Category | Percentage
A        | 33%
B        | 38%
C        | 29%

Which should be a simple calculation:

Category | Percentage
A        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%
B        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%
C        | ((Sum of Amount within group / Sum of Amount across groups) * 100)%

But I can't figure out how to do that within Report Designer (SSRS) - whenever I create a row grouping on Category, I can get the sum within the group with =Sum(Fields!Amount.Value). But how to get the sum across groups from a cell within the group?

Answer 1

You could have used Scope descriptors to identify the groups that the SUM is to be run against:

• Category group is defined in tablix Row Groups.
• DataSet1 is the name of the dataset.

Grouped Amount: [Sum(Amount)]

Dataset Total: SUM(Fields!Amount.Value, "DataSet1")

Percentage: SUM(Fields!Amount.Value, "Category") /SUM(Fields!Amount.Value, "DataSet1")