Forum Discussion
Reorder rows hierarchy in matrix
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)?
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
1 Reply
- VasTg
Memorable Member
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