Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Regular Visitor

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.