Forum Discussion
Showing measure in rows
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)
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.
2 Replies
something like this?
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.
