Hi All,

I have two tables shown below and I wish to add a calculated column to Table 1.

Table1                                      Table2

ID    Activity                             Activity 1     Activity 2    Activity 3    etc    etc   etc   Total
1    Activity 1                           values         values       values                                  Total value
2    Activity 2                           values         values       values                                  Total value
3    Activity 3                           values         values       values                                  Total value
etc

I want each row in the new column to show the corresponding activity divided by the sum of the total. So in the row with Activity 3 I want the formula SUM(Table2[Activity 3])/SUM(Table2[Total]). Also the number of activities will vary.

Cheers,

Adam

PS
I am actually wanting to calculate a more complicated formula which calculates the correlation so if anyone knowsof a better way to do this that would be great.

Hi,

I dont think you can. You will be better off unpivoting your Table2 before you load that table into PowerPivot.

So, what you want is a Custom Field (measure) with a formula like:

MyCoolSum:=SUM(Table2[Activity 3])/SUM(Table2[Total])

Then, create a Table or Matrix with Activity and MyCoolSum in there.

You can create other custom fields for the other Activities. 

Not really clear on your data, it seems a little weird to have rows and columns with the same labels, not sure what you are going for here, samples of the source data might help.