cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PEN15GGSCRUB
Frequent Visitor

Sorting Accounts by a Category

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 CodeAccount NameAccount Category Key
40930Business Networking Membership1
40941Business Networking Functions1


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:Screenshot 2022-12-01 163938.png

 

 

Screenshot 2022-12-01 164020.png

2 REPLIES 2
MacLeod
New Member

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.

 
 MacLeod_0-1669893816981.jpeg

 


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
Super User
Super User

@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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.