1
votes

I am trying to add some kind of calculated item (Excel equivalent) into a tablix I have created from a dataset.

e.g.

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000

I want to add a row below that will calculate the margin % and also be dynamic so when the figures change, so will the Margin %

e.g.

Type       Today        Month       Annual
Sales      1,000        15,000      35,000
GP           200         1,500       5,000
Margin %     20%           10%        14.3%
1
sorry the layout did not show as I wanted it to...Type the row heading with Sales and GP within...and Today, Month and Annual are all columns...hope this makes sense...sorry for any confusion - Aintarf
Do you have your initial fields already in a table? - gbeaven
Are the Sales and GP values in the same column of your dataset? It's helpful to have a small sample of how the raw data looks because that changes how you would write the calculation. - StevenWhite
Hi yes the Sales and GP are in the same column under the heading 'Type'. The raw data looks like the above as best as I can get it sorry. - Aintarf

1 Answers

0
votes

If this is in the footer, then you can conditionally sum on the field Type, for example for the Today column:

=SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing)) 

Of course, if Sales can be zero then you will get divide-by-zero errors so you need to make the expression a bit more complicated to bypass the error-generating divide-by-zero calculation of the IIF function parameters:

=IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) <> 0.0, 
SUM(IIF(Fields!Type.Value = "GP", Fields!Today.Value, Nothing)) / IIF(SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, 0.0)) = 0.0, 1.0, SUM(IIF(Fields!Type.Value = "Sales", Fields!Today.Value, Nothing))),
Nothing)