Forum Discussion

FalconBudget's avatar
FalconBudget
Frequent Visitor
7 years ago
Solved

Sort drilldown categories in matrix rows

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.

 

RevenueMiscellaneous Revenues
 Charges for Services
 Intergovernmental Revenues
 Licenses & Permits
 Fines & Forfiets
 Taxes
 Cash Balance
  
ExpensesPersonnel 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. 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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's avatar
      FalconBudget
      Frequent Visitor
      7 years ago

      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.