Showing measure in rows

[razieh12]

Hello

I have a question and appreciate if someone provide me with the solution.

My  data is 2 tables: Speed Table and Productivity Table.

Speed Table:

City	Column1	Column2	Column3
A	1	6	1
B	4	9	4
C	0	9	3
D	1	5	9
A	8	7	0
B	9	1	6
C	0	7	4
D	1	8	3

Productivity Table:

City	Fac1	Fac2
A	1	6
B	4	9
C	0	9
D	1	5
A	8	7
B	9	1
C	0	7
D	1	8

And I want to create this matrix from these tables

by grouping by the data of speed table and productivity tables and pivoting them and appending these two table I can create the below matrix:

My main challenge is the Fac1/Fac2. I dont know how to add this measure in the maxtrix. I appreciate it if some one can help me. Fac1/Fac2 means divide( Fac1,Fac2).

the values in matrix is the calculate (sum( column1)), calculate (sum( column2)),calculate (sum( column3)),calculate (sum( fac1)),calculate (sum( fac2)), divide( Fac1,Fac2)

[lbendlin]

something like this?

[razieh12 · Accepted solution]

Hello Ibendlin,

Thank you for your solution. However, I have found a better approach that I'd like to share here.

To begin, I wanted to visualize both data columns (column 1, column 2, column 3, fac1, and fac2) and the measure I created (Measure) in a matrix visualization. 

What I did was add an additional column in the productivity table using Power Query. I named this column "Measure" and inserted a value of 0. Afterward, I pivoted the table and grouped it by the productivity table with fac1, fac2, and the new column I created. Then, I appended this table with the speed table. In the speed table, I also pivoted and grouped by column 1, column 2, and column 3.

Below is the resulting table I created using Power Query.

I created this measure and drag it in the value field of Matrix:

Measure=if(SELECTEDVALUE(Append1[Attribute])="Measure",CALCULATE(SUM(Append1[Value]),Append1[Attribute]="Fac1")/CALCULATE(SUM(Append1[Value]),Append1[Attribute]="Fac2"),

CALCULATE(SUM(Append1[Value]))
and I drag the attribute to the row  of matrix and city to the column of matrix.