How to collapse and expand columns in matrix

[rs1249]

Hi Guys, 

I am creating a matrix so that value column A and value column B can be collapsed and expand to column C,

relationship between the columns is C = A + B

Matrix will be something like below:

				SUM C
ID	NAME	SUM D	SUM E	SUM A	SUM B
Job1	www	123	50	11	12
Job2	xxxx	23	34	13	14
Job3	zzzz	23	50	15	16

 so column A & B can collapes into C and expand as well.

I tryied using hierachy, tried unpivote column A and B, it just doesn't work, when i try unpivote column A and B,

the table headers end up became like below: 

		SUM A		SUM B
ID	NAME	SUM D	SUM E	SUM D	SUM E

if anyone can point me in the right direction or how me how to do this would be much appreciated.

Thanks in advance

[MFelix · Accepted solution]

Hi @rs1249 ,

This can be done following the steps below:

• Unpivot all the columns you want to have the sum (not only A and B) this will give you a similar result as below:

ID	Name	Attribute	Value
Job1	www	D	123
Job1	www	E	50
Job1	www	A	11
Job1	www	B	12
Job2	xxxx	D	23
Job2	xxxx	E	34
Job2	xxxx	A	13
Job2	xxxx	B	14
Job3	zzzz	D	23
Job3	zzzz	E	50
Job3	zzzz	A	15
Job3	zzzz	B	16

• Create the following table:

CAT	TOP CAT
A	C
B	C
E	E
D	D

• MAke a relationship 1 -> Many linking CAT with Attribute
• Setup your matrix in the following way:
  • Rows:
    • ID (Original table)
    • Name (Original Table)
  • Columns:
    • TOP CAT (New table created)
    • CAT (New table created)
  • Values:
    • Values (Original table)

Then expand on columns and rows the matrix to the last level, remove the subtotals and should give you the expected result.

Check PBIX file attach.

Regards,

MFelix