Reorder rows hierarchy in matrix

[Anonymous]

Hi All!

I faced quite an unusual request to change the order in the report to one similar we had in Excel. I have searched for any hint but didn't find anything.

Our current table in Excel has the following structure:

Country
Country 1	x
Country 2	x
Country 3	x
Country 4	x
Country 5	x
Adjustments	x
Total 3rd Party	(sum of Country and Adjustments)
Intercompany	x
Total incl. I/Co	(sum of Intercompany and Total 3rd Party)

When creating a report in PBI, we've made a dimension table like this:

CG	Group	3rd party	Total incl. I/Co
Country 1	Country Group	Total 3rd Party	Total incl. I/Co
Country 2	Country Group	Total 3rd Party	Total incl. I/Co
Country 3	Country Group	Total 3rd Party	Total incl. I/Co
Country 4	Country Group	Total 3rd Party	Total incl. I/Co
Country 5	Country Group	Total 3rd Party	Total incl. I/Co
Adjustment	Adjustment	Total 3rd Party	Total incl. I/Co
Intercompany	Intercompany	Intercompany	Total incl. I/Co

CG column was connected with other tables in the data model.

In PBI report I put all the columns from the table above to the matrix rows and get the following rows:

Total incl. I/Co

Intercompany

Total 3rd Party

Adjustment

Country Group

Country 1

Country 2

Country 3

Country 4

Country 5

Can you advise how can I get the structure similar to the one in Excel file (first table)?

[VasTg · Accepted solution]

@Anonymous 

The closest I got this is below. Definitely you could use "Sort by" to sort it in the way you want.

Create a table as below and join the key column to the fact.

Col1	Sub Col1	key
Country	Country 1	Country 1
Country	Country 2	Country 2
Country	Country 3	Country 3
Country	Country 4	Country 4
Country	Country 5	Country 5
Adjustments		Adjustments
Intercompany		Intercompany
Total 3rd Party		Country 1
Total 3rd Party		Country 2
Total 3rd Party		Country 3
Total 3rd Party		Country 4
Total 3rd Party		Country 5
Total 3rd Party		Adjustments
Total incl. I/Co		Intercompany
Total incl. I/Co		Country 1
Total incl. I/Co		Country 2
Total incl. I/Co		Country 3
Total incl. I/Co		Country 4
Total incl. I/Co		Country 5
Total incl. I/Co		Adjustments

Maybe you might want to look at Reports Builder.

If it helps mark it as a solution

Kudos are nice too