locked
How can I reference a column based on a row value? RRS feed

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

    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.

    Friday, September 4, 2015 1:52 PM

Answers

  • 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 Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao 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 Charlie Liao Tuesday, September 22, 2015 8:56 AM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:29 AM
    Friday, September 4, 2015 2:12 PM