Create a report in required format

[rob_vander]

Hi All,

I have below data. I

ID	Option	Value	Number of sites
1	abc	a	1
1	abc	b	2
1	abc	a	3
1	abc	b	4
1	def	a	5
1	def	b	6
1	def	a	7
1	def	b	8
2	abc	a	1
2	abc	b	5
2	abc	a	2
2	abc	b	7
2	def	a	5
2	def	b	6
2	def	a	3
2	def	b	1

I want to create a report in below format.

ID	Total Sites	Option	Total Option Sites	Value	Total Value Sites
1	36	abc	10	a	4
				b	6
		def	26	a	12
				b	14
2	30	abc	15	a	3
				b	12
		def	15	a	8
				b	7

[Kushal_Chawda · Accepted solution]

@rob_vander  try below. Create a table with below dimension and measure

1) Add Dimension ID with below text colour expression

=if(ID=Above(total ID,1),white())

2) Total Sites : Add Measure with expression and text colour expression 

=Sum(total <ID>[Number of sites])

text colour expression
=if(ID=Above(total ID,1),white())

3) Add Dimension Option with below text colour expression

=if(Option=Above(total Option,1),white())

4) Total Option Sites : Add Measure with expression and text colour expression 

Sum(total <ID, Option>[Number of sites])

text colour expression
=if(Option=Above(total Option,1),white())

5) Add dimension Value

6) Total Value Sites: Add measure with below expression

Sum([Number of sites])

[rob_vander]

@Kushal_Chawda  Thanks a lot.