Hi,

I'm having a difficult time with this, was hoping someone can help!

I create a matrix,

Row groups: Province

Column groups: Project Type

Values:  Count of # of Projects, Sum of Amount ($)

What this displays is the following. How do I go about calculating what's highlighted in yellow? It is Amount of OneProjectType / Amount of TwoProjectType.

Thanks!

• Edited by ssidenable 11 hours 15 minutes ago

Are you certain there will always be only 2 columns?

I'm leary of suggesting this for you, as it is fragile based on the data returned, but try something like this for an expression (which you should be able to get if you add in subtotals in the Design tab, and then click the right triangle in the upper right hand corner to customize the expression (or add a separate line/field for this expression)

=First(dataSetName!ProjectType.value) / Last(dataSetName!ProjectType.value)
(sorry, the syntax is definitely off, as I don't have the RS on this machine)
You may have to pass in the ds name as a string param to the aggregate functions.

Hi, 

According to your description, you want to add a calculated column to the matrix, right? 

In our local environment, we have tested this scenario and your requirement can be achieved by using custom code. In your scenario, since we need to calculate the amount based on the column values dynamically. For you requirement, we can use custom code to store the column value, and then when to use them based on your requirement. Please refer to the following steps: 

1. Add the following custom code into your report. (You may modify the "OneProject" based on your ProjectType)
   

Public Shared Value1 as Integer

Public Shared Value2 as Integer

Public Shared OneProject as string

  Public Shared Function GetValue(Value as Integer,projectType as String) as Integer

If projectType = "OneProject" Then

     Value2=Value

Else 

Value1=Value

End If

     return Value

  End Function

  Public Shared Function GetPct()

     return Value2 / Value1

  End Function

Create a matrix like below:

Expr1 expression: =Code.GetValue(Fields!Amount.Value,Fields!ProjectType.Value)
Expr2 expression: =Code.GetPct()

The result is like below: 

If you have any question, please feel free to ask.

Regards,
Shrek Li

• Edited by Shrek LiMicrosoft contingent staff, Moderator 3 hours 49 minutes ago