switch value depends on rows in matrix

[Anonymous]

Hi, Here the sample data, I created a matrix that drag the column1, dim1, dim2 to rows, and measure to values,

I want the row subtotal not display total of each category, rather show the column1 level return the max value of value1, and each element of dim1 display value2, and if expand to dim2, show the value2, is there a soution to create such a measure?

columu1	dim1	dim2	value1	value2
A	A1	aaa	7	6
A	A1	bbb	7	3
A	A2	ccc	5	4
A	A3	ddd	8	2
A	A3	eee	8	9

here is the expected result of matrix visual

			measure
A			8
	A1		7
		aaa	6
		bbb	3
	A2		5
		ccc	4
	A3		8
		ddd	2
		eee	9

[amitchandak · Accepted solution]

@Anonymous , You can use isinscope in a new measure

Switch( true(),

isinscope(Table[dim2]), Sum(Table[Value2]) ,

isinscope(Table[dim1]), Max(Table[Value1]) ,

isinscope(Table[Col1]), Max(Table[Value1]) )