Calculating percentages inside of same matrix group

[mbryantuk]

Hi

I have a report containing a matrix, this matrix has a month column across the top and two groups Market and Response, so the report ends up looking like
Oct-07
GRAND TOTAL 100%
England 10%
Accept 7%
Reject 4%
US 5%
Accept 2%
Reject 1%

so each row is a percentage of the grand total (100%) ideally i would like the accept and reject to be a percentage of the market total so they total 100% and the Market to be a percentage of the grand total,

GRAND TOTAL 100%
England 90%
Accept 54%
Reject 46%
US 10%
Accept 5%
Reject 95%

The dataset contains
Market,Response,cnt

England,Accept,500
England Reject,450
US,Accept,10
US, Reject,30

Currently the expression looks like

=IIF(InScope("matrix1_RowGroup2"),
IIF(sum(Fields!cnt.Value) = 0, 0,
Sum(Fields!cnt.Value)/sum(Fields!cnt.Value, "matrix1_RowGroup1")),
IIF(Fields!total.Value=0, 0,
Sum(Fields!cnt.Value)/Fields!total.Value))

Fields!total.Value is a calculated field in my dataset which gives a grand total of the cnt field

Many thanks

Matt

 

[xlbo]

Have a look at the ReportITems collection

You should be able to reference the subtotal "cell" in your percentage calc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question