# How can I reference a column based on a row value?

• ### Question

• 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,

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.

Friday, September 4, 2015 1:52 PM

• 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.

• Proposed as answer by Tuesday, September 22, 2015 8:56 AM
• Marked as answer by Thursday, October 8, 2015 9:29 AM
Friday, September 4, 2015 2:12 PM

### All replies

• Hi,

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

Friday, September 4, 2015 2:01 PM
• 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.

• Proposed as answer by Tuesday, September 22, 2015 8:56 AM
• Marked as answer by Thursday, October 8, 2015 9:29 AM
Friday, September 4, 2015 2:12 PM