Creating an unpivoted table of measures

[ben_E_556]

Hello,

I have multiple tables connected via relationships. In my simplified example, there are three tables with utility data that are connected by utility name. Some of these tables have multiple years of data per utility, some do not.

I am looking to unpivot a table with one row per utility where some columns are measures that summarize multiple years of data. I'm able to create a table with one row per utility with multiple summarization measures in my data view, but I cannot unpivot this table of measures to create a long table with 3 columns (utility, measure, value). 

For context, I'm looking to create a correlation grid where I compare multiple metrics together. I'm looking to be able to compare average values by utilility with overall scores by utility. 

In order to create the correlation grid, we need a data frame where all values are in one column. I've tried various ways, but I'm getting stuck trying to create a correlation matrix between the summarized measures.

Pasting example data below and the correlation formula with screenshots.

This screenshot shows my correlation matrix for one of the tables. I'm unable to get summarization measures from other tables in the matrix. I would like "average percent_adv" and "average outage length" and "average outage min" in the below matrix.

utility	Year	percent_adv
F	2021	0.01011538
I	2021	0.9483634
G	2021	0.61273015
D	2021	1
C	2021	0.73086045
A	2021	0.94885022
J	2021	1
B	2021	0.97884053
H	2021	1
E	2021	1
F	2020	0.00016183
I	2020	0.74107892
G	2020	0.55757024
D	2020	1
C	2020	0.67148985
A	2020	0.95288265
J	2020	1
B	2020	0.98621083
H	2020	1
E	2020	1
F	2019	0.00012355
I	2019	0.59810882
G	2019	0.57252318
D	2019	0.99995823
C	2019	0.35195303
A	2019	0.92042736
J	2019	1
B	2019	0.86401994
H	2019	1
E	2019	1
F	2018	0.00012698
I	2018	0.47154228
G	2018	0.57484172
D	2018	0.99288994
C	2018	0.0455672
A	2018	0.84680691
J	2018	1
B	2018	0.53876631
H	2018	1
E	2018	1
F	2017	0.00010812
I	2017	0.27741826
G	2017	0.54328045
D	2017	0.9568745
C	2017	0.00325753
A	2017	0.75493245
J	2017	1
B	2017	0.21102577
H	2017	1
E	2017	1

utility	Year	outage_min	outage_length
A	2021	111.2	1.311
B	2021	65.379	0.88
C	2021	200.4	2.57
D	2021	264.13	3.1
E	2021	50.08	0.81
F	2021	57.89	0.84
G	2021	164	0
H	2021	5.11
I	2021	3.997	2.866
J	2021	41.994	0.844
A	2020	108.211	1.363
B	2020	90.6	0.821
C	2020	225.192	2.842
E	2020	36.26	0.66
F	2020	59.32	0.868
G	2020	237
H	2020	351.42
I	2020	206.4	2.45
J	2020	31.827	0.52
A	2019	125.82	1.47
B	2019	85.536	0.774
C	2019	280.133	3.23
E	2019	51.37	0.5
F	2019	15.895	0.486
G	2019	265.2
H	2019	444.84
I	2019	270.6	2.88
J	2019	52.707	0.974
A	2018	153.8	2.06
B	2018
C	2018	294.942	3.338
E	2018	40.055	0.5
F	2018	14.587	0.4
G	2018	138.6
H	2018	289.08
I	2018	230.36	2.607
J	2018	37.266	0.84
A	2017	131.86	1.87
B	2017
C	2017	220.061	1.995
E	2017	32.305
F	2017	12.441	0.003
G	2017	299.4
H	2017	216.06
I	2017	381.72	1.479
J	2017	28.619	0.585

utility	customer_score	employee_score
A	135	111
B	161	133
C	110	81
D	147	114
E	145	81
F	107	39
G	162	75
H	148	85
I	141	86
J	128	80

Correlation Coeff =

 DIVIDE(

     [n_utility]*[XY] - [X]*[Y],

     SQRT(

         ([n_utility]*[X^2] - [X]^2) * ([n_utility]*[Y^2]-[Y]^2)

     )

 )

Y^2 =

 VAR CurrentX = SELECTEDVALUE('Attributes (2)'[Attribute])

 VAR CurrentY = SELECTEDVALUE(Attributes[Attribute])

 VAR Virtual =

 SUMMARIZE(

     'Score Pivot',

     'Score Pivot'[utility],

     "X",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentX),

     "Y",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentY)

 )

RETURN

SUMX(

    Virtual,

    [Y] * [Y]

)

Y =

 VAR CurrentX = SELECTEDVALUE('Attributes (2)'[Attribute])

 VAR CurrentY = SELECTEDVALUE(Attributes[Attribute])

 VAR Virtual =

 SUMMARIZE(

     'Score Pivot',

     'Score Pivot'[utility],

     "X",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentX),

     "Y",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentY)

 )

RETURN

SUMX(

    Virtual,

    [Y]

)

XY =

 VAR CurrentX = SELECTEDVALUE('Attributes (2)'[Attribute])

 VAR CurrentY = SELECTEDVALUE(Attributes[Attribute])

 VAR Virtual =

 SUMMARIZE(

     'Score Pivot',

     'Score Pivot'[utility],

     "X",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentX),

     "Y",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentY)

 )

RETURN

SUMX(

    Virtual,

    [X] * [Y]

)

X^2 =

 VAR CurrentX = SELECTEDVALUE('Attributes (2)'[Attribute])

 VAR CurrentY = SELECTEDVALUE(Attributes[Attribute])

 VAR Virtual =

 SUMMARIZE(

     'Score Pivot',

     'Score Pivot'[utility],

     "X",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentX),

     "Y",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentY)

 )

RETURN

SUMX(

    Virtual,

    [X] * [X]

)

X =

 VAR CurrentX = SELECTEDVALUE('Attributes (2)'[Attribute])

 VAR CurrentY = SELECTEDVALUE(Attributes[Attribute])

 VAR Virtual =

 SUMMARIZE(

     'Score Pivot',

     'Score Pivot'[utility],

     "X",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentX),

     "Y",CALCULATE(MAX('Score Pivot'[Value]), 'Score Pivot'[Attribute] = CurrentY)

 )

RETURN

SUMX(

    Virtual,

    [X]

)

[v-shex-msft]

HI @ben_E_556,

Current power bi does not support create dynamic calculated column/table based on filter effects. They do not work on the same data level, and you can't use child level to affect its parent.

Measure expressions are calculated based on row and filter context, use them in table/column will fix the calculation result and not able to respond with filter effects.

In my opinion, I'd like to suggest only create the table with specific category and measure names, then you can write formula with switch function to check current category values to look up and redirect to different calculation results.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng