Sort drilldown categories in matrix rows

[FalconBudget]

Howdy. I am working with a large financial data file and need to report out in a specific order. I have tried creating indexes and then sorting by column, but that doesn't seem to work (at least the way I was doing it). For your reference I have three main tables- the raw financial data- by cost center & cost element, a master key for both cost center & cost element to determine groups, and a ranking table for those groups as listed below. 

Below I have the matrix as shown in our old excel matrix (where you can just drag the items). It goes Revenue vs Expense, Cost Element group, Cost Element Subgroup, and then the cost element itself. 

Old Excel Matrix

This is how it should look- each with the ability to drill down/up.

Revenue	Miscellaneous Revenues
	Charges for Services
	Intergovernmental Revenues
	Licenses & Permits
	Fines & Forfiets
	Taxes
	Cash Balance
Expenses	Personnel Services
	Maintenance & Operations
	Fixed Costs
	Debt Service
	Contributions & Transfers
	Capital Outlay
	Reserves

This is how it looks like in my matrix now.  After too many hours of trying to decipher other posts on rank order index I decided to post here. 

Bi Matrix in wrong order

Any and all help would be appreciated. 

[MFelix · Accepted solution]

Hi @FalconBudget ,

Assuming that your revenue / Costs are columns in your data.

Why don't you add a table with the following format:

Index	Category	Detail
1	Revenue	Miscellaneous Revenues
2	Revenue	Charges for Services
3	Revenue	Intergovernmental Revenues
4	Revenue	Licenses & Permits
5	Revenue	Fines & Forfiets
6	Revenue	Taxes
7	Revenue	Cash Balance
8	Revenue
9	Expenses	Personnel Services
10	Expenses	Maintenance & Operations
11	Expenses	Fixed Costs
12	Expenses	Debt Service
13	Expenses	Contributions & Transfers
14	Expenses	Capital Outlay
15	Expenses	Reserves

Then make a relationship between the last column and your data table.

Use the ID column to sort the Detail column and use this table for your matrix visual should work.

Regards,

MFelix

[FalconBudget]

I think I finally figured out my problem, which your comments led me to realize. I wasn't using my new table as the item in the matrix, but rather the old designation and acting confused about why it couldn't be sorted. 

It worked once I tweaked the list to add more detail and pulled it in directly. 

THANK YOU.