compare column name to cell value and calculate values

[hwilliamson-hw2]

I need to calculate the total value of a column in Table 2, referenced by a cell value containing the column name in table 1, and post the SUM in Table 1. I'm trying to figure out the calculation I need to use in the second column of my table.  I can't figure out how to reference a column in a function based on the value of a cell. 

I'm thinking that I need to do this in Power Query M functions... but I'm stuck 🙂  Any ideas on how I can do this?

Table_1

COLUMN1               COL_CALCULATION

col name 1               SUM(Table_2[col name 1])

col name 2               SUM(Table_2[col name 2])

col name 3               SUM(Table_2[col name 3])

col name 4               SUM(Table_2[col name 4])

etc.....

Table_2

CLIENT               col name 1       col name 2      col name 3      col name 4      etc.

client 1               integer             integer             integer             integer

client 2               integer             integer             integer             integer

client 3               integer             integer             integer             integer

client 4               integer             integer             integer             integer

etc...

[amitchandak]

@hwilliamson-hw2 , In DAX,

But you should prefer Power query

switch( True(),
table[COLUMN1] ="col name 1" , sum(Table2[col name 1]),
table[COLUMN1] ="col name 2" , sum(Table2[col name 2]),
table[COLUMN1] ="col name 3" , sum(Table2[col name 3]),
table[COLUMN1] ="col name 4" , sum(Table2[col name 4])
)

[danextian]

hello

Table2  needs to be transformed into a structure that will make it easy to do DAX or allow a relationship. Below M code unpivots all columns other than CLIENT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JTM0rUTBU0lEyNAASpmCWhVKsDlzOCChiYQwkjECEMYocSMTMBESAVJkYIMuBhE1BhAlIztBIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLIENT = _t, #"col name 1" = _t, #"col name 2" = _t, #"col name 3" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"CLIENT"}, "Column Name", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

After Table2 has been unpivoted, you may:

Create a relationship between 'Table1'[COLUMN 1] and 'Table2'[Column Name] and then create a measure that sums the value of 'Table2'[Value]

Create a calculated column in Table 1 with or without an existing relationship with Table2 that sums up 'Table2'[Value]

CLIENT TOTAL CALC COLUMN =
CALCULATE (
    SUM ( 'Table2'[Value] ),
    FILTER (
        ALL ( 'Table2' ),
        'Table2'[Column Name] = EARLIER ( 'Table1'[COLUMN 1] )
    )
)​

[hwilliamson-hw2]

With CALCULATION and FILTER used, it blocks all other filters. For instance, i have a page filter for CLIENT that lets my CTO walk through the data either by client, group, or all (depending upon who he is presenting to).

So this got me VERY CLOSE to what i needed, Now i just have to figure out how to adjust it to keep filters working.

Thank you!!!