Cell Value as Column Reference in Calculated Column

[Anonymous]

Hi everyone,

I am looking for a solution to dynamically reference to columns in another table based on values in a column of the current table. Similar as it done with '@Columnname' in Excel.

A seen in the screenshots, I would like to replace the '@Activity' with an expression that references the columns in Table 2 based on the cell value of the column Activity in Table 1 so that the respective averages are calculated.

Thanks in advance!

Jonas

New table that should hold averages calculated from table PG_Maturity_2020_01 (2)

Table: PG_Maturity_2020_01 (2)

[az38]

Hi @Anonymous 

go to the Power Query editor, pick ID column in PG_Maturity_2020_01 table, right-click, Unpivot Other Columns

then in report mode create relationships by [Activity] and PG_Maturity_2020_01[Attribute] values

then create a measure

AverageFrequency = AVERAGE('PG_Maturity_2020_01 (2)'[Value])

[parry2k · Accepted solution]

@Anonymous you have to create a dynamic measure

My Measure = 
SWITCH ( SELECTEDVALUE ( Table[Activity] ),
 "Value1", AVERAGE ( Table2[Column1] ),
 "Vale2", AVERGE ( Table2[Column2] ),
 "Vale3", AVERGE ( Table2[Column3] )
)

replace table and column name as per your model.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!