The way that you would use Power BI to analyse departments in Sage 50 depends on how you have set up your Sage 50 departments module. There are two main ways to use Sage 50 departments:
- Departments as a way to group transactions. The departments module in Sage 50 enables you to group transactions of any type for reporting. In this way, you can create departmental profit and loss reports and balance sheets by department. “Company within a company” reporting, if you like.
- Departments to enable segmentation by customers, suppliers and/or products. Many people also use departments for simpler segmentation of their customers, suppliers and/or products.
In this article, I highlight the main things that you should consider if you are using Power BI to analyse your Sage 50 departments.
Sage 50 Accounts department backgrounder
Sage 50 Accounts can support up to 1,000 departments. The definition of each department is in the departments table. Each row in the departments table contains a department number (0 through to 999) and a name. If you bring all of your Sage 50 Accounts tables into Power BI using ODBC (directly or indirectly) you will end up with department information in a variety of tables (the original ODBC names are in brackets):
Content seriesView full content series
Sales invoices (INVOICE)
Sales invoice details (INVOICE_ITEM)
Sales orders (SALES_ORDER)
Sales order details (SOP_ITEM)
Purchase orders (PURCHASE_ORDER)
Purchase order details (POP_ITEM)
The department table contains a master list of all of the departments. The other tables just carry the relevant department information for each row of that table.
Structuring Sage 50 department information in Power BI
The first thing to note straight away is that it is neither correct nor possible to use the departments table as a parent for departmental information in the other tables. A parent - child relationship between departments and customers would not be compatible with a parent - child relationship between departments and sales invoices. Also, it is possible for a sales invoice to have a different department from its customer.
The only use of the departments table is to provide a list of department numbers and names. Both these fields are available in each of the ODBC tables that contain departmental information. So the only functions of the departments table in Power BI are; if you want to list all the departments, or to populate one of the other tables with the department name if (as I did in my first iteration of my Power BI model) you have omitted to include it in your data source.
In such a case you can simply use the LOOKUPVALUE function in DAX:
Customer department =
LOOKUPVALUE('Departments'[Name],Departments[Dept Number],[Dept Number])
Here I am using the [dept number] field in my customers table to lookup the department name in the departments table, even if there is no relationship defined.
What if my customer department and sales invoice department are different?
In Sage 50 it is possible that a sales transaction has a different department for each of the steps in the sales order-to-cash process and in each corresponding table:
Sales order details
Sales invoice details
The same applies to the Sage 50 purchasing process. So if my department is different for each of these steps along the sales process for the same transaction and the same customer, which department do I use for my reports? Well, the answer is “it depends”.
To understand that, I will explain a little about how departments operate in Sage 50 Accounts. A background on how Sage 50 manages departments.
Default department Values
In Sage 50 Accounts there are three master data tables that hold default department values; customers, products and suppliers. If a particular customer has a default department of “3”, then by default transactions created for this customer can inherit department “3”
It gets a bit more complicated then if a transaction uses Products that have different Department defaults. It is even more complicated if there are multiple lines on a Sales Invoice each with different default product departments. Which department is to be the default? This is explained in this Sage 50 help document though my own testing produces different results. This doesn’t really matter though if you follow the guidelines that I propose in this article.
For finance departments that group transactions, the “correct” department to use is in your transactions table
At every step of a transaction, no matter what the default Department values are, it is possible for the Sage 50 user to edit the Department value.This makes perfect sense, but it is very important to understand the implications of this if you are producing departmental reports in Power BI (or indeed in any other reporting tool). If you edit the department of a child record (e.g. Transactions) this does not change the department in a parent record (e.g. customer).
A sales by department report in Power BI can, therefore, produce different results depending on whether you are selecting the department from the customers, products, sales invoices, sales invoice lines or transactions table.
If you are using departments to group transactions (potentially mixed transaction types), then it is the department information in the transactions table that you should use, since the transactions table is the final resting place for any Sage 50 transaction.
Slicing and summarising your “transaction departments”
Of course one of the great things about Power BI is that you can slice and dice your departmental information in a number of ways – in this case by any attribute of the transactions table or related table, such as dates (I recommend that you create a dates table and relate the transaction date in transactions to this), customers, suppliers.
There is a problem with roducts though. This is a tricky one. The problem here is that your transactions table does not carry any link to your products table.
It does carry the item description which in the case of a product transaction is probably the same as the product description. It may not be though because it is editable in Sage.
Also, at some point, you may have edited the master product description in the products table. This would change the product description for subsequent lines in the transactions table but not previous ones. Both cases will cause Power BI to “think” that these records are different products when in fact they are the same.
Using departments in this way for simple segmentation is very quick and easy to setup.
Well if you are lucky enough to know, or can figure out, which products are affected by this then in Power BI desktop there is a workaround.
In addition to simple filtering in or out particular description, you can use the “grouping” functionality in Power BI. Grouping allows you manually to combine records in a list and treat them as one.
For departments that are used to segment customers, products and/or suppliers
The departments module in Sage 50 is very powerful because you can use it to group any transaction type together; costs as well as income, journals as well as receipts and payments. If your needs are not so advanced, you can instead use departments for more simple segmentation of your customers, products, or suppliers and their transactions.
In this case, you are simply using the default department of a customer, product or supplier as a way to group these entities and their child transactions for reporting purposes (in this case you are not for example grouping costs and customers). Let’s say that you wanted to group customers together as part of a common sales region then you could use departments for this.
Further granularity can be achieved by changing the department allocated to a transaction such as a sales invoice.
Further granularity can be achieved by changing the department allocated to a transaction such as a sales invoice if you wanted to allocate different sales to the same customer to different sales reps.
Using departments in this way for simple segmentation is very quick and easy to setup and you can use departments to segment all three tables; customers, suppliers and products. If you are using departments in this way, then it is the department in the respective table(s) that you would use in Power BI and not the department from the transactions table.
Sage 50 Accounts departments can add very useful depth to your Power BI model.
If you are using departments to group potentially any types of transaction for reporting purposes, then you will need to use the department information in the transactions table in Power BI and ignore the department information in other tables. You may need to apply some further grouping Power BI desktop if you want to slice you department by product(s).
If you simply want to use departments to segment customers, suppliers and/or products then you should use the department information held in these tables or their child transactions tables.
About Hugh Johnson
I am the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50. Please feel free to try it out in connected to your own Sage 50 system on the web or with Power BI Desktop.
I am a senior vice president of Suntico, an online financial data platform that works with popular small business accounting software. I am responsible for the company’s customer division. This covers products, marketing, sales, customer on-boarding, customer service and renewals. I am also in charge of our product strategy and road map.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high performance Fireball dinghy racing and food.