Sorting Accounts by a Category

Hi! I own a small analytics consulting company and we work with small-medium enterprises to implement Power BI and/or Power Pivot reporting.

Many of our customers have a department/sub-department reporting requirement like yours. We create a “table of contents” style for their sorting requirements that expands on the “sort by” abilities. For one account, they go 4 levels deep: Department, Sub-Department, Sub-sub-Department, Project. This table is kept in a file so that the table can be added to the reports, joined to the data model as needed.

While this might look confusing, it is quite simple: Columns B through E (not shown) contain the names of the departments, sub-departments, sub-sub-departments and Projects (Example: Maintenance, Cables, Fibre optics, Upgrade from CAT-5).

Then in columns F through I, manually fill in the order sequence desired. In this case, Maintenance comes first, so all Maintenance entries get a 1. There is no need to start at “1” if you are very consistent in the order. Each of the sort columns is independent within the table, and you can safely re-use numbers for the next group: 1-1-1-1 will not interfere with group 2-1-1-1 in any way.

Since this Sort table will likely be used for many years, it is a quick method of maintaining a complex reporting order. Just remember to set up the “Sort-by” order whenever a new report is created!

We use a similar table to track the account codes and so sort them for reporting revenues and expenses. The two Sort-by tables co-exist quite nicely and allow the customer to report revenues and expenses by either Project or by Account Code.

I hope that this helps, feel free to contact me if I can clarify any of this.