Divide Two Cells in Matrix

Question

Post reply

Divide Two Cells in Matrix

Triality

SSCommitted

Points: 1958
More actions
May 18, 2012 at 3:08 pm

#261230

Hello -
For the last two days I have been struggling to figure out how to divide two cells in a Matrix. Maybe this is the wrong way to think about it....another way of saying it...dividing a conditional total sum by a total row sum from the data set. Here is where I am at:
I have the following matrix built:
[/URL]
And here are the results:
[/URL]
I need to build an expression to populate the G% column correctly, which is simply (SUM(Sales) WHERE Indicator = "G")/SUM(Sales). So in this example it would be the following calculations:
23,386 / 28,574 = 81.84%
23,602 / 28,810 = 81.92%
11,839 / 16,025 = 73.88%
12,476 / 16,837 = 74.10%
12,023 / 15,974 = 74.27%
So far my expression looks like this:
=(IIF(Trim(Fields!Indicator.Value) = "G",Fields!Sales.Value, 0)/SUM(Fields!Sales.Value))
But it is obviously returning 0.00% for all fields. Another funky thing is if I change the expression to look for Indicator.Value = "B" it seems to work just fine:
[/URL]
Any help would be greatly appreciated!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 1

The output you show for "B" is not correct. It is a fluke that the results for B seem to work in the top part.

JK/201202 B= 4186 Sum=16025 so B% should be 4186/16025 = 0.2612 or 26.12% (not 3.63% as shown)

I think this indicates that only the last record included within the group is being used for the formula. As luck would have it the G column does not occur as the last entry so 0.00%.

You need to change the formula to sum the values if indicator is "G":

=(SUM( IIF(Trim(Fields!Indicator.Value) = "G",Fields!Sales.Value, 0)) /SUM(Fields!Sales.Value))

Fitz

peseta30 Old Hand Points: 328 More actions · Answer 2

Hello

I think you need to read more about using ReportItems is the only way too do it a lot faster.

- First find the name of the indicator textbox - do this by looking at the properties

- do the same for the 2 other textboxes that contains the data

once that is done then do the following in your G column

Have in mind that I'm using sample textboxes names:

Textbox1 =Indicator Cell

Textbox9 = is your value of sales cell

Textbox12 = line total cell

=iif(trim(ReportItems!Textbox1.Value) ="G", (ReportItems!Textbox9.Value / ReportItems!Texbox12.Value),0)

You don't need to reference your fields anymore. I suggest you read more about ReportItems that is going to make your report a lot easier to build. The main reason why you have to use ReportItems is because your matrix expands and collapses automatically

I hope this helps.

:-):-)

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 3

peseta30 (5/19/2012)
Hello
I think you need to read more about using ReportItems is the only way too do it a lot faster.
- First find the name of the indicator textbox - do this by looking at the properties
- do the same for the 2 other textboxes that contains the data
once that is done then do the following in your G column
Have in mind that I'm using sample textboxes names:
Textbox1 =Indicator Cell
Textbox9 = is your value of sales cell
Textbox12 = line total cell
=iif(trim(ReportItems!Textbox1.Value) ="G", (ReportItems!Textbox9.Value / ReportItems!Texbox12.Value),0)
You don't need to reference your fields anymore. I suggest you read more about ReportItems that is going to make your report a lot easier to build. The main reason why you have to use ReportItems is because your matrix expands and collapses automatically
I hope this helps.
:-):-)

This assumes that only one column can have that indicator ("G"). If more than one has it only the last value is returned. To return multiples then a sum function must be used, in this case sum(Field!x.Value) and sum(ReportItems!ObjectName.Value) are the same.

If you are focussing on the performance only, then the calculation should be done in the dataset underlying the matrix and not the matrix itself using a windowed aggregation function.

😉

Fitz