Sorting Accounts by a Category

[PEN15GGSCRUB]

Hi all,

I have a chart accounts that I want to organise in a custom order. Picture 1 below for reference.
The chart of accounts data that I have imported does not contain the categories in bold, i.e. Business networking, Sponsorship Other and Player Sponsorship. There are 51 of these categories.

My first idea was to create a separate sorting table for the Account Categories, such as in Picture 2. then use SWITCH function in the Chart of accounts table to assign an Account Category key in that table. It would look like this:

Account Code	Account Name	Account Category Key
40930	Business Networking Membership	1
40941	Business Networking Functions	1

I would then create a relationship based on Account Category Key between chart of accounts table and account category table. Unfortunately there are 200+ accounts that I would need to categorise with SWITCH, which seems tedious and an unnecessary waste of time.

My question is this: is there anyway that I can make this process simpler/faster? Or should I just bite the bullet and categorise each account by hand?

Picture 1:

[MacLeod]

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.

[amitchandak]

@PEN15GGSCRUB , You need to have sort column , if you are creating based on same column, then you need to do like

account category 1 = [Account Category]

account category sort = <Switch sort code>

Mark account category sort as sort column for account category 1  and use that in visual

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

I have done something similar here, But I have done in excel

refer

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU