Aggregates in Matrix Reports

I am having a similar issue in a Matrix report. The following is a message from someone else having the same problem.

I have a calculation (Aggregate) that has a scope that is for both the Row and the Column.

Example:

What is the percent of total sales and percent of total category sales?
How do I do these Percentages in a Matrix?

Q1 Q2 Total
Beverages

Lemonade
Sales $100 150 250
% of Bev 44%
Percent of Total 19%

Soda
Sales $125 125 250
% of Bev 46%
Percent of Total 22%
Total Beverages $225 275 500

Snacks
Peanuts
Sales $100 150 250
% of Snacks
% of Total

Cookies
Sales $ 200 250 450
% of Snacks
% of Total
Total Snacks 300 400 700
Total Sales 525 675 1300

Any Ideas?

Assuming you have a matrix called "Matrix" with a row grouping called "CategoryGroup", you can calculate percentages in matrix cells using aggregate functions with explicit scope names.
E.g. for the percentage of sales within the current category group:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "CategoryGroup")

Percentage of sales within total sales:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "Matrix")
Or:
=Sum(Fields!SalesAmount.Value) / Sum(Fields!SalesAmount.Value, "DataSetName")

-- Robert|||What if I am not using a matrix. I just have a report that is now counting the items in subfields and I also want to include a percentage of the total that the subfield is.
animals
cats
lions 30
tigers 20
pumas 50
bears
grizzly 10
etc.
total 200
I want to include a percentage column
animals
cats
lions 16 8%
tigers 10 5%
pumas 30 15%
etc.
|||

Not sure I understand. It sounds like you are using a table with table groups. Is the "total" calculated for the entire table, or for individual groups?

In both cases, you can just use a similar approach for calculating the percentages:

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1_AnimalTypeGroup")

=Sum(Fields!amount.Value) / Sum(Fields!amount.Value, "table1")

-- Robert

|||

I have a similar question. I too am using a matrix for my report. It is an employee headcount report. I have row groups for dept and employee ID and I have column groups for the month and the headcount. I also have parameters on Dept and month. The data shows as follows:

Dept Emp ID# Emp Name Jan Feb March

HR 123 H. Potter 1 1 1

IT 231 R. Weasley 1 1 1

Sales 879 C. Digory 1 1 0

I want to format the report so that if there is a change in the headcount number for an employee, the row is highlighted in a different color. So in the example provided above, the last column would be highlighted in red.

I think the logic would be to add up the total of the row sum(fields!headcount.value) and divide it by the number of months (parameter!month.count) and then compare the value in the first column (January) to the result.

However I am not sure of the exact syntax for this formula nor am I sure where to place this formula within the layout of the report. Should I add another column grouping? Or would this formula go somewhere in the group properties for the employee?

Any help would be greatly appreciated

Linda

|||Robert, you are great. This example helped me to get the results I wanted.