How to change colums to rows and current rows to colums DAX

[Matti78]

Hello,

I have been wondering how to change colums to rows and vice versa. I have tried to check community and see if i find something from youtube, but with no luck.

I have this kind of table:

Table1

System	Hours	Weighted average	Project1	Project2	Project3	Project4	Project5
Sub1	11516,5	30,86 %	17,3	7,72	25,34	11,68
Sub2	3374	9,04 %			6,55
Sub3	6833,45	18,31 %	13,7	2,14	13,73
Sub4	8931,5	23,93 %	16		16,32
Sub5	2953	7,91 %			5,93
Sub6	2219,5	5,95 %	3,9		4,46
Sub7	484,5	1,30 %			0,13
Sub8	651,5	1,75 %
Sub9	354,4	0,95 %

 and i would like to create new table where data would be like this:

Table2

Project	sub1	sub2	sub3	sub4	sub5	sub6	sub7	sub8	sub9
Project1	17,3		13,7	16		3,9
Project2	7,72		2,14
Project3	25,34	6,55	13,73	16,32	5,93	4,46	0,13
Project4	11,68
Project5

I have tried summarize command, but i did not manage get dax code correctly.

Any help with this issue?

[Ashish_Mathur]

Hi,

In the Query Editor, select the first 3 columns, right click and select "Unpivot Other Columns".  Rename the Attribute column to Projects.  Build your visual from this transformed table

[Anonymous · Accepted solution]

Hi @Matti78 

You can refer to the following example

1.Create a table

2.Then create a measure

Measure = var _project1=SUMMARIZE('Table',[Project1],[System],"Projects","Project1")

var _project2=SUMMARIZE('Table',[Project2],[System],"Projects","Project2")

var _project3=SUMMARIZE('Table',[Project3],[System],"Projects","Project3")

var _project4=SUMMARIZE('Table',[Project4],[System],"Projects","Project4")

var _project5=SUMMARIZE('Table',[Project5],[System],"Projects","Project5")

var _table= UNION(_project1,_project2,_project3,_project4,_project5)

return SUMX(FILTER(_table,[System] in VALUES('Table'[System])&&[Projects] in VALUES('Table (2)'[Column1])),[Project1])

3.Select matrix visual, then put the column of table2 to row, put system of table to column, then put measure to the value.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Matti78]

Hello,

I added measure code 4 new projects same way as you discriped in your earlier message, but for some reason it dosen't freshresh those 4 new projects to matrix table. Any idea how to fix it?

Measure 2 =

var _project1=SUMMARIZE('Weighted average',[Project1],[System],"Projects","Project1")

var _project2=SUMMARIZE('Weighted average',[Project2],[System],"Projects","Project2")

var _project3=SUMMARIZE('Weighted average',[Project3],[System],"Projects","Project3")

var _project4=SUMMARIZE('Weighted average',[Project4],[System],"Projects","Project4")

var _project5=SUMMARIZE('Weighted average',[Project5],[System],"Projects","Project5")

var _project6=SUMMARIZE('Weighted average',[Project6],[System],"Projects","Project6")

var _project7=SUMMARIZE('Weighted average',[Project7],[System],"Projects","Project7")

var _project8=SUMMARIZE('Weighted average',[Project8],[System],"Projects","Project8")

var _project9=SUMMARIZE('Weighted average',[Project9],[System],"Projects","Project9")

var _project10=SUMMARIZE('Weighted average',[Project10],[System],"Projects","Project10")

var _project11=SUMMARIZE('Weighted average',[Project11],[System],"Projects","Project11")

var _project12=SUMMARIZE('Weighted average',[Project12],[System],"Projects","Project12")

var _project13=SUMMARIZE('Weighted average',[Project13],[System],"Projects","Project13")

var _table= UNION(_project1,_project2,_project3,_project4,_project5,_project6,_project7,_project8,_project9,_project10,_project11,_project12,_project13)

return SUMX(FILTER(_table,[System] in VALUES('Weighted average'[System])&&[Projects] in VALUES(Progress[System])),[Project1])

[Matti78]

Thank you very much!!!