Power BI with your accounting data

Hugh Johnson

Hugh Johnson
Creator of "Accounting Insights for Sage 50" that is the only Microsoft Power BI Content Pack available for Sage 50. Senior vice president of Suntico, responsible for anything from the company that my customers or prospects can see or touch. Master of Engineering from City University London and a Post Graduate Diploma in International Selling from Dublin Institute of Technology. My personal passions are high performance Fireball dinghy racing and food.
Find me on:

Recent Posts

Connect Power BI to Sage 50 Accounts - Part 4 of 4

Posted by Hugh Johnson on 09-Dec-2018 11:06:44

Connect Power BI to your Sage 50 OData Feed

This blog describes part four of a four-part process to connect Power BI to Sage 50 using the Suntico BI service.  In this blog I show you how to link your Sage 50 company (or companies) that you have in your Suntico account to Microsoft Power BI.  We will look at two examples:

  1. Connecting your Sage 50 company to the "Accounting Insights for Sage 50" Content Pack on the Power BI Service.
  2. Connecting your Sage 50 company to Power BI Desktop.

You can do either or both.  Maybe it is a good idea to connect to the Content Pack first, as that will give you a ready-made dashboard and report that you can start to use right away.  Meanwhile, you can connect to Power BI Desktop and start to build out some custom reports is you alsow wish to.  To get you started with Power BI Desktop you can also download a Power BI Template file that will save you a lot of work setting up your Sage 50 model.

Let's start with the common activity that you will nee to do, and that is to generate your Power BI keys (username and password) for the Suntico BI OData feed.

Read More

Topics: Sage 50, Power BI, Sage 50cloud, Accounting, Reporting, Connect Power BI to Sage 50, ODBC

KPIs for Accounts Receivable Tracking

Posted by Hugh Johnson on 15-Oct-2018 08:20:38

A Baker's Dozen of KPIs for Accounts Receivable tracking

If you have objectively tried to measure the performance of your collections team, you will know just how difficult this is to do well.  In my opinion, usefulness has often been traded for measurability as getting the answers out of standard accounting software systems has been too difficult.  In this blog list some of the most common KPIs for Accounts Receivable, and discuss their uses and limtations.

  1. Days Sales Outstanding (DSO)
  2. Average DSO
  3. Best Possible DSO (BPDSO)
  4. Overdue Ratio
  5. Average Age
  6. Weighted Average Age
  7. Average Days to Pay
  8. Weighted Average Collection Days (WACD)
  9. Weighted Average Terms (WAT)
  10. Average Days Delinquent (ADD)
  11. Weighted Average Days Delinquent
  12. Accounts Receivable Turnover Ratio
  13. Collections Effectiveness Index (CEI)
Many KPIs are in this demo
Read More

Topics: Accounts Receivable

Power BI Treemap for Accounts Receivable analysis

Posted by Hugh Johnson on 12-Sep-2018 07:00:43
Read More

Topics: Accounts Receivable

Power BI tip: use Boolean columns to add a simple DAX filter

Posted by Hugh Johnson on 05-Sep-2018 06:15:25

In Power BI, something that you often want to do is create aggregations based on filtered sets of your data.  This may be aggregations based on time, such as YTD or by geography such as "Within the EU". 

In DAX there are many functions available to create a filtered set of your data.  Wherever the DAX function includes <filter> as a parameter, such as with the CALCULATE or FILTER functions, then Boolean TRUE/FALSE columns in your dataset can make this very easy.  In the our Power BI template for Sage 50 we have added many Boolean TRUE/FALSE columns to the raw data from the Suntico BI OData feed, as well as to some additional Calculated Columns such as [IsReceivable] in the BITransSplits table.  This makes it really easy to add filters to any Measures that you may want to create to perform aggregations of your data.

Read More

Topics: DAX

Power BI vs Tableau: who is winning the mindshare?

Posted by Hugh Johnson on 02-Sep-2018 19:44:19

Is Power BI in danger of becoming a generic term?

We search for something on the internet, we "Google" it.  When we vacuum the floor, we "Hoover" it.  There are many examples of brand names that have become generic terms.  Could Power BI be going the same way for business intelligence?

Read More

Topics: Power BI

My favourite three Power BI DAX functions

Posted by Hugh Johnson on 30-Aug-2018 07:53:46

DAX (Data Analysis Expressions) is the language used to write formulae in Power BI when you want to create new Calculated Columns or Measures.  In many respects it is similar to the way you write formulae in Excel.  Stated more simply, DAX helps you create new information from data already in your model.  While it is easy to make visuals in Power BI from your existing data model, we regularly want to see information for particular time periods, or with specific filters applied or create new calculated values based on our raw data.  There are some super free online resources available to learn DAX, which will help you to turn your simple Power BI model into something great.  Learning just a few basic functions of DAX can assist you with unlocking numerous new bits of knowledge from your data.  Here I introduce my favourite DAX functions that I use in the Accounts Receivable Solution for Sage 50 and the Power BI Template for Sage 50.


Read More

Topics: DAX, Power BI

DAX Running Total Calculation in Power BI

Posted by Hugh Johnson on 29-Aug-2018 19:04:03

What is a running total?

Read More

Topics: Power BI, DAX

Power BI Adoption By Country

Posted by Hugh Johnson on 29-Aug-2018 07:03:04

Microsoft does not publish figures for Power BI adoption by country.  I am not surprised, but it is something that I wanted at least some kind of feel for to help me plan the development and roll-out of Suntico BI.  So as a proxy for relative adoption rates by country, I looked at monthy Google search volume per capita for the term "Power BI".  I picked a few mature English-speaking countries for which Power BI would have been available for the same length of time, and that for various reasons were countries that we were interested in targeting for our service.  The results are below.

Read More

Topics: Power BI

Sage 50cloud Accounts - what is it?

Posted by Hugh Johnson on 26-Aug-2018 09:38:32

What is Sage 50cloud Accounts?

Sage 50cloud Accounts is the latest name for Sage 50c Accounts, that was formerly known as Sage 50 Accounts and before that Sage Line 50.  It is a desktop accounting product family published by Sage Group plc primarily for the UK and Irish markets. 

Read More

Topics: Sage 50 Accounts

Accounts Receivable - what they are and why they are important

Posted by Hugh Johnson on 22-Aug-2018 13:15:57

What are Accounts Receivable (aka Trade Receivables)?

Accounts Receivable (also known as Trade Receivables) are monies owed to you by customers whom you have invoiced for products or services supplied but for which you have not yet received payment.  Your Trade Receivables are an asset (since you expect to receive cash from them) and in your accounting software, your total trade receivables will be the balance of your Debtors Control Account on your Balance Sheet. 

Read More

Topics: Accounts Receivable, Sage 50 Accounts

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts